Getting Grand Total and Subtotals using SQL Server Window Functions
Did you know that you can use SQL Server’s window function in an aggregate, and partition by NULL to get a grand total?
Let me illustrate. In the example below I use my trusty ol’ pubs database. (Hey, it’s still a great database for simple examples. Great for people starting to learn SQL too).
Let’s get some basic numbers first so we know what we are expecting to get.
Using a basic query that gets some grand total, we get 493. This the total for the whole table.
SELECT SUM(qty) FROM sales
Now let’s look at a query that groups this by stor_id, and reports the subtotal per stor_id.
SELECT stor_id, SUM(qty) FROM sales GROUP BY stor_id
What if we wanted to see all the individual details AND also the grand total AND the store subtotal in the same row? We can take advantage of the SUM with an OVER clause to force the partition. For the grand total, we partition by NULL. For the subtotal per store, we partition by the stor_id.
SELECT stor_id, ord_num, qty, SUM(qty) OVER (PARTITION BY stor_id) total_for_store, SUM(qty) OVER (PARTITION BY NULL) total_for_table FROM Sales ORDER BY stor_id