Re: SQL : subtotals and total

David FOXWELL wrote:

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

I've used something like this before (this particular example is untested):

SELECT 1 as ord,id, product, level, sum(number)
FROM table
GROUP BY id, product, level

UNION ALL

SELECT 2 as ord, id, product, ' ', sum(number)
FROM table
GROUP BY id, product

ORDER BY id,ord

In order to satisfy the conditions for UNION ALL, you will probably have to play around with setting the field length for the derived fields like ord and the ' ' in the second SELECT.

Hope this helps,
Adam