SQL for subtotals & grand total?

[Zoon]

This query is for an Access 97 database and it works OK. With it I get a total cost for each department. Department is not the key field, there are 40 fields. There are 50 different departments in the Department field & 10,000 records.

SELECT [Department], COUNT([Department]) AS [SKU Count], SUM([Quantity]*[Cost]) AS [Department Cost] FROM [Products]

GROUP BY [Department]

The question is, how can I get the total cost of every item in every department included in this query? A grand total at the bottom of the [Department Cost] column, if possible.

 

[BNPMike]

SQL is really to pull out a set of records, not to solve end-to-end business problems. Channel your query into a report, which can do all that for you and control formatting/display etc

 

[PHV]

A grand total at the bottom of the [Department Cost] column
SELECT [Department], COUNT([Department]) AS [SKU Count], SUM([Quantity]*[Cost]) AS [Department Cost]
FROM [Products]
GROUP BY [Department]
UNION
SELECT "Grand Total", COUNT(*), SUM([Quantity]*[Cost])
FROM [Products]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244