query: Automatic Subtotals 1

[bperry]

Have you ever wanted to show subtotals directly in your query output, and have SQL automatically calculate them for you? It's pretty easy. I was looking at WITH ROLLUP tonight, and these were the results I got.

First, let's see an ordinary query that summarizes on State and Store.
[tt]
Select State,
StoreName,
SUM(qty) as Sales
From Sales sa INNER JOIN Stores st
ON sa.StoreId = st.StoreId
Group By State, StoreName

State StoreName Sales
----- --------------- -------
CA Books R Us 60
CA News & Brews 90
WA Back Pages 8
WA Book Beat 130

(4 row(s) affected)
-------------------------
[/tt]

No problem. Now, to get some subtotal lines, just add WITH ROLLUP.
[tt]
Select State,
StoreName,
SUM(qty) as Sales
From Sales sa INNER JOIN Stores st
ON sa.StoreId = st.StoreId
Group By State, StoreName
WITH ROLLUP

State StoreName Sales
----- --------------- -------
CA Books R Us 60
CA News & Brews 90
CA NULL 150 ** new line!
WA Back Pages 8
WA Book Beat 130
WA NULL 138 ** new line!
NULL NULL 288 ** new line!

(7 row(s) affected)

There's your subtotals, automatically calculated!
-------------------------
[/tt]


To change the NULLs to something more pleasing, you could easily do this:
[tt]
Select ISNULL(State,'') as State,
(CASE
When State IS NULL then 'Final Total'
When StoreName IS NULL then '--Subtotal'
Else StoreName
END) as StoreName,
SUM(qty) as Sales
From Sales sa INNER JOIN Stores st
ON sa.StoreId = st.StoreId
Group By State, StoreName
WITH ROLLUP

State StoreName Sales
----- --------------- -------
CA Books R Us 60
CA News & Brews 90
CA --Subtotal 150
WA Back Pages 8
WA Book Beat 130
WA --Subtotal 138
Final Total 288

(7 row(s) affected)
-------------------------
[/tt]

That's it. Pretty cool.

bperry