Totals and subtotals 1

[estersita]

Hi,

Could you please help me with the following problem?

I have a following table name Messages:
******************
msg_code totals
******************
sssss 2
ccccc 3
sssss 5
ccccc 1

I would like to get a sum of totals by msg_code
******************
msg_code totals
******************

sssss 7
ccccc 4

I tried the following script:

compute sum of totals on err_num
break on msg_code skip 1
select msg_code,totals
from Messages
;

However I do not receive what I expect. What I am doing wrong? Any help will be appreciated

Thank you,

EStersita

 

[SantaMufasa]

Estersita,

Try this code:

break on report
compute sum of totals on report
select msg_code, sum(totals) totals from messages group by msg_code;

MSG_C     TOTALS
----- ----------
ccccc          4
sssss          7
      ----------
sum           11

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:17 (04Sep04) UTC (aka "GMT" and "Zulu"), 23:17 (03Sep04) Mountain Time)

 

[zephan]

I heard ROLL UP can do the job.

 

[estersita]

SantaMufasa,

I tried it. For some reason I have the following result:
MSG_C TOTALS
----- ----------
ccccc 40
sssss 70
----------
sum 110

I am confused how it could happen that "0" is attached to each total? Do you see what I did wrong?

Thank you very much for your help with this problem

Estersita

 

[SantaMufasa]

Estersita,

To isolate your problem, please post a copy-and-paste the following code and the results of their executions:

1) SELECT * FROM MESSAGES;
2) Your summation code and results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:34 (04Sep04) UTC (aka "GMT" and "Zulu"), 10:34 (04Sep04) Mountain Time)

 

[estersita]

SantaMufasa,
1 I run :
SELECT * FROM MESSAGES
2 I got the following result:

******************
msg_code totals
******************
sssss 2
ccccc 3
sssss 5
ccccc 1
******************
sum 11

which is strange...because of the sum which is not supposed to appear.
I wonder...if I can use totals as column name? I have a feeling that it might be an ORACLE reserved word and therefore it calculate the sum when it is not asked at all?

 

[SantaMufasa]

Estersita,

1) "Totals" is the name of your second column in the "MESSAGES" table. It is not an Oracle reserved word.

2) The reason that "Sum 11" appears is because previously in this same SQL*Plus session (and it could have been yesterday even), you issued the commands:

break on report
compute sum of totals on report

These SQL*Plus commands stay in effect until you either explicitly "CLEAR BREAK" and "CLEAR COMPUTE" or you log out of your SQL*Plus session. They mean that a) there is a "control break" set for the end of every query and b) following any query that outputs a column entitled "TOTALS", there will be output that SUMs the numeric contents of the "TOTALS" column.

You can confirm my assertion by issuing the following SQL*Plus commands at the prompt:

break

compute

The above commands display the behaviour you have currently specified for both "breaking" and "computing".

So, you actually did ask for the SUM of the TOTALS column, but your request may have been as long ago as yesterday.

Now, back to your most recent question: 'Why "0" is attached to each total?' Since you did not post your "0"-inclusive code along with the output, I still cannot tell you why it's happening, especially with the table contents you display in your just-previous post. Please do copy and paste the code and spurious output.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:12 (04Sep04) UTC (aka "GMT" and "Zulu"), 14:12 (04Sep04) Mountain Time)

 

[estersita]

SantaMufasa,

Last time I provided you just with a simpified fragment.
This is my real script named ErrCalcRangeFlnk_.sql:

break on report
compute sum of totals on report
select e.err_num, sum(e.totals) totals
from err_sum_stat e, reports r
group by e.err_num
;
Below please see what I copied and pasted:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> @ErrCalcRangeFlnk_.sql

ERR_N TOTALS
----- ----------
66666 70
ccc22 100
sss11 130
----------
sum 300

SQL>

p.s The totals in my Err_SUM_STAT table are the followings:
****************
ERR_Num TOTALS
******************
66666 1
sss11 5
sss11 8
ccc22 10
66666 6
******************

Is that what you asked me about? Sorry if I misunderstood you again

Thank you,

Estersita

 

[SantaMufasa]

Estersita,

Your REAL code makes all the difference in the world! And it also explains why you are receiving the extra '0' on the the end if each line. Notice that your REAL code reads not only from the "err_sum_stat" table, but also from the "reports" table. Reading from two tables means you are doing a two-table JOIN. When we join two or more tables, we use a WHERE clause to specify how to match the records from the joining tables. If you do not specify a WHERE clause to match rows, the result is a Cartesian Product. A Cartesian Product results in every row in Table_A matching with every row in Table_B. Since your results are exactly ten times greater than you want them to be, I will bet my annual salary that there are 10 rows in your Table_B (the "reports" table).

Since I do not see any reference in your SELECT statement to any columns from the "reports" table (i.e., "reports" is not contributing anything to your results), then I recommend your simply removing ", reports" from the FROM clause and rerunning your SELECT. I'll bet that works for you.

Please post your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:13 (05Sep04) UTC (aka "GMT" and "Zulu"), 17:13 (04Sep04) Mountain Time)

 

[estersita]

SantaMufasa,
Thank you,Now I see it...It is definetely Cartesian Product without join. The thing is that REPORTS table does contribute as I am planing to add a condition:

break on report
compute sum of totals on report
select r.run_date,e.err_descr,e.err_num,r.cust_num,e.totals, sum(e.totals)
from err_sum_stat e, reports r
group by e.err_num
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').

Currently I am trying to "isolate" a problem.

Now I am tried to do the following join (no date condition yet):

break on report
compute sum of totals on report
select r.run_date,e.err_descr,e.err_num,r.cust_num,e.totals, sum(e.totals)
from err_sum_stat e, reports r
group by e.err_num
where r.rpt_id=e.rpt_id
;

I got a message:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> @ErrCalRangeFlnk_.sql
SP2-0310: unable to open file "ErrCalRangeFlnk_.sql"
SQL> @ErrCalcRangeFlnk_.sql
where r.rpt_id=e.rpt_id
*
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL>

It makes me think that I should use HAVING instead WHERE.
However I also failed with Having....

 

[SantaMufasa]

Estersita,

Use the HAVING clause when you want to filter grouped-row output based upon the results of a group function. An example of a good use of HAVING in your case would be if you wanted to see msg_codes whose TOTALS were >= 10...you would say something like:

break on report
compute sum of totals on report
select r.run_date,e.err_descr,e.err_num,r.cust_num,e.totals, sum(e.totals)  
from err_sum_stat e, reports r
where r.rpt_id=e.rpt_id
group by e.err_num
having sum(e.totals) >= 10
;

In your case, you still want the WHERE clause, but you just have it in the wrong place. It should appear as I have it in the simulated code, above...always immediately following the FROM clause.

Once you move your WHERE clause, you would then receive errors on your columns "r.run_date","e.err_descr","r.cust_num", and "e.totals". This is because once you SELECT even one expression that is a group expression (i.e., SUM(e.totals)), then each and every expression in your SELECT clause must also be a group expression. You took care of that issue with "e.err_num" by listing it in your GROUP BY clause ("group by e.err_num"). You could solve that same problem with those other expressions by also listing them in your GROUP BY clause, but at that point, your output report becomes nearly meaningless because at that point, every detail line becomes a group of its own and I do not think that is what you want.

I'd be happy to help you formulate the correct SELECT statement if you would please post sample output that you would like the SELECT to produce. (When you post sample output, you can produce column alignment if you begin your output listing with "{code}" and end the section with "{/code}", but use square brackets ("[" and "]") instead of curly braces.)

I'm happy to work more on this tonight until you are satisfied...what time zone are you in?

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:30 (05Sep04) UTC (aka "GMT" and "Zulu"), 21:30 (04Sep04) Mountain Time)

 

[estersita]

SantaMufasa,

I live in Boston...

The output I would like to receive is the following:
[
ERR_N TOTALS
----- ----------
66666 7
ccc22 10
sss11 13
----------
sum 30
]
I moved WHERE clause but still failed with a following:

SQL> @ErrCalcRangeFlnk_.sql
Enter value for run_date1: 04-aug-2004
old 5: having TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AN
D
new 5: having TRUNC(r.run_date) BETWEEN to_date('04-aug-2004','DD-MON-YYYY') A
ND
Enter value for run_date2: sep-08-2004
old 6: to_date('&run_date2','DD-MON-YYYY')
new 6: to_date('sep-08-2004','DD-MON-YYYY')
having TRUNC(r.run_date) BETWEEN to_date('04-aug-2004','DD-MON-YYYY') AND
*
ERROR at line 5:
ORA-00979: not a GROUP BY expression

I run the following script:

break on report
compute sum of totals on report
select r.run_date,e.err_descr,e.err_num,r.cust_num,e.totals, 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
;

I tried to do it with date range condition as well as without:
break on report
compute sum of totals on report
select r.run_date,e.err_descr,e.err_num,r.cust_num,e.totals, sum(e.totals)
from err_sum_stat e, reports r
where r.rpt_id=e.rpt_id
group by e.err_num
;

However I still have the same message
ORA-00979: not a GROUP BY expression

p.s I even tried to exclude all fields from select except totals...all the same

I hope I provided you with all data...


I am totally confused...

 

[SantaMufasa]

Estersita,

You mention above that, "The output I would like to receive is the following:"

ERR_N     TOTALS
----- ----------
66666         7
ccc22        10
sss11        13
      ----------
sum          30

To achieve that output your code should read:

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
;

I do not understand why you are trying to SELECT columns that you don't want to display such as: r.run_date, e.err_descr, r.cust_num, and e.totals. You may reference those columns in the WHERE clause without listing them in the SELECT's expression list.

Try the adjusted code I posted and let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:21 (05Sep04) UTC (aka "GMT" and "Zulu"), 13:21 (05Sep04) Mountain Time)

P.S. The reason your code posting didn't appear as a code list is because you leterally need to begin the code post with the following character string (minus the hyphens):
"[-c-o-d-e-]"
Then you terminate the code listing with the following characters string (minus the hyphens):"[-/-c-o-d-e-]"

 

[estersita]

SantaMufasa,


It works now! Thank you very much for you great help, patience and consideration.

I really appreciated.

With respect,

Estersita