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

sum_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

sum_sales_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

sum_partition_by_null

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s