Summary of a summary possible?

[MCubitt]

In a single SQL statement, can one provide a GRAND total of grouped by totals?

EG.

select
part, sum(qty)
from
partsinstock
group by part

produces:
Part Sum(qty)
A 100
B 20
C 100
D 80

and I want
Part Sum(qty)
A 100
B 20
C 100
D 80
TOTAL 300

Possible? Please!




Applications Support
UK

 

[tpsmith]

Try this

select
part, sum(qty)
from
partsinstock
group by part
UNION
select
'TOTAL' part, sum(qty)
from
partsinstock

 

[stefanhei]

Possible with analytics:

select
 part, sum(qty) 
from
 partsinstock
group by rollup(part)

Stefan

 

[SantaMufasa]

Although I prefer the simplicity of Stefan's suggestion, if you wish to leverage SQL*Plus's built-in report formatting capabilities, you can also say:

col x heading "Total|Quantities" format 999,999
break on report
compute sum of x on report
select part, sum(qty) x
  from partsinstock
 group by part;

Let us know your eventual preferences.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:01 (05Jan05) UTC (aka "GMT" and "Zulu"),
@ 11:01 (05Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[MCubitt]

Thanks you all. Since we wanted a single SQL statement to do it, the first 2 statements do the job.

I am unsure of any benefits of one over the other so assume either will do.

Thanks yee three wise men!



Applications Support
UK