detail and sub total

timbal25 · June 9th, 2004, 09:40 AM

How can I perform sub totals on my sql result set? Here is my basic sql code.

select year, month, customer, itemgroup, amount
from orders
where year = 2004

--- result set --------

Year Month Customer ItemGroup Amount
---- ----- -------- --------- ------
2004 1 123 ABC10 10
2004 1 123 DEF20 20
2004 2 123 ABC10 15
2004 2 456 ABC10 20
2004 2 456 DEF20 10

How do I get the result to group by and total on month or customer or item group? The following is what I envision as a result set.

Year Month Customer ItemGroup Amount
---- ----- -------- --------- ------
2004 1 123 ABC10 10
2004 1 123 DEF20 20

Total Amount Month 1: 30

Year Month Customer ItemGroup Amount
---- ----- -------- --------- ------
2004 2 123 ABC10 15
2004 2 456 ABC10 20
2004 2 456 DEF20 10

Total Amount Month 2: 45

Do I need to write this in pl/sql?
Any advice is appreciated.
-Tim

owain · June 9th, 2004, 03:23 PM

Try this:

Code:

  SELECT Month SUM(Amount)
    FROM Orders
   WHERE Year = 2004
GROUP BY Month

This will return the total amount for each month in the yaer of 2004.

Regards
Owain Williams

timbal25 · June 9th, 2004, 03:49 PM

Thanks Owain for your reply.

Yes if I use group by I will receive total for the month.
However, I want to see the detail records as well (as depicted in my original post).

Thanks,
-Tim

happygv · June 9th, 2004, 08:13 PM

Hi Tim,

Code:

select year, month, customer, itemgroup, amount
from    orders
where   year = 2004
GROUP BY Month
ORDER BY Month
COMPUTE SUM(Amount) by Month

This should get you there.
Cheers!

_________________________
-Vijay G

Strive for Perfection