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

[rcherch]

saritha,

ocp_dev did what I was speaking of for reports.

I am wanting to know, do you want to see the detail and then the sub totals and grand totals or just the sub totals and grand totals.

if you are desiring the first type:
in Report Builder Data Model Section
1. Create your query
I think the query would like
select substr(tc_line_rev_acct_no,1,5) actgrp,
tc_line_rev_acct_no, totamtpd
from table_x
2. Pull out the actgrp from G_actgrp (actgrp might be changed by Report Builder). This will create two G_NAME in the data model.
3. Generate your report using a Group above method.

Else:
you can use a nested query..for the second part.

Good Luck!