SQL to recieve double subtotals

[Kay]

Hello,
I'm trying to put together the SQL to recieve subtotals
per employee AND that subtotal divided into subtotals per
product. The problem could be applied to Northwind
(Employees, Orders, Order Details).
Can anyone give me a hint on how to get two subtotal
levels?
Tia,
Kar

 

[Michel Walsh]

Hi,


With MS SQL Server, that is a CUBE, or a ROLLUP. With JET, a
possible solution is to merge the two totals query with an UNION ALL:


SELECT employee, Null As product, Sum(Amount) As sumOf FROM somewhere GROUP
BY employee
UNION ALL
SELECT Null, product, SUM(Amount) FROM somewhere GROUP BY product
UNION ALL
SELECT Null, Null, SUM(Amount) FROM somewhere



Note that I include a third query, making the overall total. I mimic a CUBE
in that I use NULL for the field I aggregate over: in the first query, I
don't care about the product, so, the second column is always NULL; in the
second query, I don't care about the employee, so I use NULL for the first
field; in the big total, I don't care about neither, so, a NULL in each the
first and the second field. Note that the fields get their name from the
first SELECT: employee, product, sumOf.



Hoping it may help,
Vanderghast, Access MVP