Subquery in order to display other fields in addition to totals 1

[estersita]

Hi,

Recently I got a great help from SantaMufasa (thread 759-911843) with resolving totals calculations problem.I did got totals I needed! Now I realized I also need other fields (except err_num)to be dispayed. Since the nature of Group by does not allow it, I assumed I need a subquery for that purpose:

I tried the following one:

select r.run_date,e.err_descr,sum(e.totals),r.cust_num,e.err_num,e.totals
from e.err_su_stat, reports r
where sum(e.totals) in(
break on report
compute sum of totals on report
select e.err_num,sum(e.totals)
from err_sum_stat e, reports r
where r.rpt_id=e.rpt_id AND
TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AND
to_date('&run_date2','DD-MON-YYYY')
group by e.err_num
) AND r.rpt_id=e.rpt_id
;

I got the following message:

SQL> @ErrCalcRangeFlnkT.sql
Enter value for run_date1: 04-aug-2004
old 9: TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AND
new 9: TRUNC(r.run_date) BETWEEN to_date('04-aug-2004','DD-MON-YYYY') AND
Enter value for run_date2: 08-sep-2004
old 10: to_date('&run_date2','DD-MON-YYYY')
new 10: to_date('08-sep-2004','DD-MON-YYYY')
where sum(e.totals) in(
*
ERROR at line 3:
ORA-00934: group function is not allowed here

***************************************************
Is there any way I can do it at all or it is not possible to display other fields calculating totals by jus one of them (err_num)?


Any help would be greately appreciated

Thank you,

Estersita

 

[SantaMufasa]

Estersita,

Hi, it's SantaMufasa again. I hope you don't mind my helping out again on this thread. If you want different output from your other thread, could you please post a sample of how you want the output to appear?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:25 (08Sep04) UTC (aka "GMT" and "Zulu"), 17:25 (07Sep04) Mountain Time)

 

[estersita]

Hi SantaMufasa,

I am glad you are here.

It is all the same problem but at that time it seemed to me that those 2 fields were enough (and I was so happy to get them!)
Now I need all other fields along with that results to be just displaid (not to group by them as with err_num).
Since code asks substitution variable (date range) I believe at least a single date-last date of the date range would be enough .
I would like it to look like the following output:


ERR_N TOTALS ERR_DESCR cust_num run_date
----- --------------------------------------------------
66666 7 zip code invalid 11111 04-jul-04
ccc22 10 coverage invalid 22222 05-sep-04
sss11 13 cancelled 11111 04-aug-04
----------
sum 30

Thank you very much!

Estersita

 

[SantaMufasa]

Estersita,

It appears to me that you want a detail report instead of a group summary report. (I infer this from your data columns that you want for output: Unless you have large counts of records that share the same "err_num"
plus "err_descr" plus "cust_num" plus "run_date", then grouping them will be extraneous/meaningless.

Therefore, I believe you want a detail report from the following code:

break on report
compute sum of totals on report
select	 e.err_num
	,e.totals
	,e.err_descr
	,r.cust_num
	,r.run_date  
from err_sum_stat e, reports r 
where	r.rpt_id=e.rpt_id
  AND	TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AND
				  to_date('&run_date2','DD-MON-YYYY')
;

Please run the above code and advise us of your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:14 (08Sep04) UTC (aka "GMT" and "Zulu"), 18:14 (07Sep04) Mountain Time)

 

[carp]

Also, in the query you posted, you have
"break on report
compute sum of totals on report"
in your subquery. These are SQL*Plus commands, and have no meaning (outside of "please foul up my query") within your SQL query.
Also, your query has

where sum(e.totals) in(
...
select e.err_num,sum(e.totals)...

The problem you will run into here is that you are comparing one value (sum(e.totals)) with two values
(e.err_num, sum(e.totals)). This will give you grief (and an error) because you have to have a 1-to-1 correspondence between the values. To get around this, you would probably want to reword this as

where sum(e.totals) in(
select sum(e.totals)...

These are general comments for future reference; when in doubt, go with SantaMufasa's code (I always have and never regretted it!).

 

[estersita]

SantaMufasa,

I really need subtotals BY err_num. You provided me with this code last time

Current code does not counts subtotals. It counts just total.

It looks like I do need Group by(or any other way) to get What I got last time + plus all other fields displayed

Current screen looks like the following:
SQL> @errCalcRangeFlnkT.sql
SP2-0310: unable to open file "errCalcRangeFlnkT.sql"
SQL> @ErrCalcRangeFlnkT.sql
Enter value for run_date1: 03-jul-2004
old 8: AND TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY')
AND
new 8: AND TRUNC(r.run_date) BETWEEN to_date('03-jul-2004','DD-MON-YYYY'
AND
Enter value for run_date2: 08-sep-2004
old 9: to_date('&run_date2','DD-MON-YYYY')
new 9: to_date('08-sep-2004','DD-MON-YYYY')

ERR_N TOTALS ERR_DESCR
----- ---------- ------------------------------------------------------------
CUST_NUM RUN_DATE
---------- ---------
66666 1 zip code/state combo invalid
11111 03-JUL-04

sss11 5 rejected due to other errors
11111 04-AUG-04

sss11 8 cncl invalid-sub not found
11111 05-SEP-04


ERR_N TOTALS ERR_DESCR
----- ---------- ------------------------------------------------------------
CUST_NUM RUN_DATE
---------- ---------
ccc22 10 coverage exceeds retro limit
11111 06-JUL-04

66666 6 smth else
11111 07-JUL-04

----------



ERR_N TOTALS ERR_DESCR
----- ---------- ------------------------------------------------------------
CUST_NUM RUN_DATE
---------- ---------
sum 30


It looks like I do need Group by(or any other way) to get what I got last time + plus all other fields displayed

Estersita

 

[estersita]

carp,

I did tried your suggestion
where sum(e.totals) in(
select sum(e.totals)...
but I still have an error message :
ORA-00934: group function is not allowed here

estersita

 

[SantaMufasa]

Estersita,

Remember, all database vendors' GROUP BY commands work the same way: the result of any GROUP BY is one representative row for ALL rows that possess the same, matching GROUP criteria. Secondly (recalling from your other thread from this weekend), once you SELECT even one expression that is a GROUP expression, then each and every other SELECTed expression must also be a GROUP expression either via a group function (such as SUM(), COUNT(), MAX(), MIN(), AVG(), et cetera) or via mentioning the expression in a GROUP BY clause.

In your case, to display for each output row the expressions you want [including SUM(e.totals)], then all expressions, except for SUM(e.totals), must appear in your GROUP BY clause. If you do that, then each and every row becomes a group of its own...and that is NOT what you want.

I do, however, have a "happy medium" for you. Here, then, are your requirements that I can produce for your output:
1) Each output row must display ERR_N, TOTALS, ERR_DESCR, CUST_NUM, RUN_DATE.
2) The SELECT must display the SUM of totals based upon ERR_N.
3) The SELECT must display the grand total of all ERR_N.

Here, then, is code that should give you all of the above:

set pagesize 500
break on err_num skip 1 report skip 1
compute sum of totals on err_num report
select     e.err_num
    ,e.totals
    ,e.err_descr
    ,r.cust_num
    ,r.run_date  
from err_sum_stat e, reports r 
where    r.rpt_id=e.rpt_id
  AND    TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AND
                  to_date('&run_date2','DD-MON-YYYY')
;

Please try it out and let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:09 (08Sep04) UTC (aka "GMT" and "Zulu"), 20:09 (07Sep04) Mountain Time)

 

[estersita]

SantaMufasa,

Thank you very much for the explanation. And it works now!


Estersita