RE: SQL : subtotals and total

["Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>]

I'm not in a position to check, does db2 have the rollup function? That's what you're looking for.

-Walden

--
Sent from my wireless device.

-----Original Message-----
From: David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent: 06 August, 2008 05:07
To: midrange-l@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
Subject: SQL : subtotals and total

Hi,

I have the feeling this has been answered already but I can' t find it.

I have to produce the following :

ID  Product Level  Total


I've created a view that does select sum (number) as total with group by  ID, product, level, etc.

This gives a total for each level of a product.

I also have to add the total of all the levels so I've created a view over my first view to do this.

My final table would look like this :

ID  Product Level  Total
1    A          1        2
1    A          2        6
1    A                    8

To get there I'm creating the first view, then a second on that view, then doing 2 inserts based on those views.
Its all looking very messy.


I think there is a way to produce subtotals and an overall total with the OVER clause.

Does anyone know if this is possible to do?

Thanks