Query!!

[saritha]

I want a Query which displays the sum of all 10,000 rev_acct_no, sum of all 53 rev_acct_no, sum of all 72 rev_acct_no and also the over all sum...

TOTAMTPD TC_LINE_REV_ACCT_NO
-------- ---------------------------------------
243.71 10000-2430-22111
35 10000-2430-27112
4450.3 10000-2430-27131
45.5 10000-2430-27131-44
417.9 10000-2430-27132
105.7 10000-2430-27133
2711.54 10000-2430-27134
1729.81 10000-2430-27135
1880.61 10000-2430-27136
50 10000-2430-27212
250 10000-2430-27214
------------------------------
----total1............................................................??

TOTAMTPD TC_LINE_REV_ACCT_NO
-------- ---------------------------------------

37.9 53000-3420-24806
--------------------------------------
----total2-------------------------------------------------------??

TOTAMTPD TC_LINE_REV_ACCT_NO
-------- ------------------------------------
236.15 72013-2430-5230

-------------total3-----------------------------------------??

Over all total=total1_total2+total3-----?


this is what should be displayed in REports(developer reports)
how do I write the query or do teh reports...
How does the formula column in reports serve the purpose

[aph]

You might looking for

GROUP BY rev_acct_no;

[saritha]

Originally posted by aph
You might looking for

GROUP BY rev_acct_no;

The Group by willl give me over all sum of all amount for all rev_Acct_code....but I can't get the the in the format I posed earlier........It gives me the sum of all amount for all rev_Acct_codes..but not individual.......I want this query for REports....Is there any way in the reports that can solve the purpose

[rcherch]

hi saritha,

do you have an account type field anywhere?

if not, can you substr off the front the account #(ie substr(rec_act_no,1,5))? and use that as your group for your report.

I would assume you would use group above then to list your data set.

When using the gui in Reports 6i you can say which is a total column..which should take care of your group total and overall total.

I hope this helps, if not tell me where I went wrong so we can get it for you.

[aph]

Hi Saritha,

Please give me the answer NO if you are not looking the following kind of soloution. I would appreciate if you could explain your problem such a more detail, clear and simple way so I/we can understand and solve your problem in 5mins.


SQL> select sum(sal), deptno from emp
2 group by deptno;

SUM(SAL) DEPTNO
--------- ---------
8750 10
10875 20
9400 30


Thanks
aph

[saritha]

I want the sum for all 10,000 rev_acct.......sum for all 72,000 accts....sum for all 40,000.....Displaying the amt & code for each acct..& I want the over all sum..

For EG:
Rev_code totamtpd

A1 20$
A2 30$
A3 50$
A4 90$
------------
190$-------total for A's

B 120$
---------
120$------------total for B's

C 90$
--------
90$---------------total for C's

Overall total which is 400$ (190+120+90)should be displayed....


I want the sum for all A1's ...sum for all B's which is 120$ itself....sum of C which is 90$

[aph]

Thanks its clear now, give us some time we will solve your problem.

Thanks
aph

[chrisrlong]

Looks like a typical ROLLUP/CUBE problem.

- Chris

[ocp_dev]

Assuming we have the following test1 table. Tried to do this question using break and sum commands in SQL*plus. Hope this helps.


SQL> select * from test1;

COL1 AMT
------------------------------ ----------
A1 1
A2 2
A3 3
A4 4
A5 5
A6 6
A7 7
A8 8
A9 9
A10 10
B21 21

COL1 AMT
------------------------------ ----------
B22 22
B23 23
B24 24
B25 25
B26 26
B27 27
B28 28
B29 29
B30 30
C4 4
C5 5

22 rows selected.

1 SELECT COL1, AMT, SUBSTR(COL1,1,1) FIRST
2* FROM TEST1

SQL> BREAK ON FIRST
SQL> Sum of Amt on First
SQL > /

COL1 AMT F
------------------------------ ---------- -
A1 1 A
A2 2
A3 3
A4 4
A5 5
A6 6
A7 7
A8 8
A9 9
A10 10
---------- *

COL1 AMT F
------------------------------ ---------- -
55 s
B21 21 B
B22 22
B23 23
B24 24
B25 25
B26 26
B27 27
B28 28
B29 29
B30 30

COL1 AMT F
------------------------------ ---------- -
---------- *
255 s
C4 4 C
C5 5
---------- *
9 s

22 rows selected.


--Anurag.

[saritha]

Thanks, but I need the reports in Developer reports....not SQL reports.....that output should be seen on DEveloper 2000 reports 5.0 but not on SQL........

Saritha