You might be familiar with aggregate functions in Microsoft SQL Server. These functions give you a way to aggregate your data, for example: a sum of a numeric value grouped by one or more fields in your result set. A common business requirement might be to create a result set that shows for each record in your table, the OrderID, Region and Sales. That query might look like this …
The results would show every record: [Order ID], Region and Sales amount.
In my case, there were 8,399 records returned by this query. For each row, I got an [Order ID], Region and Sales amount associated with a sale in my orders table. What if we wanted to get the sum of sales for each state? We might write an aggregate query that looks like the following:
This query would show one record for each region, with the sum for that region.
This is a good way to get to the results we want, but what if we wanted to get the total sales for each region AND state? Working with simple aggregate functions in SQL Server getting the results we want might be difficult.
This query gives us the sum of sales at the State level, but not the overall sales at the Region level. For example, we see that in the Central region, South Dakota had $75,172.774 in sales. However, we don’t know what the sum of sales was for the Central region overall.
A window function can help us create the query we really want:
Now the results show us each region and state with totals at the Region AND State level. Notice that the RegionalSales total is the sum of all the StateSales total. This opens up a lot of options for us. For example, we can now calculate what percent of sales each state contributes to the region overall.
This is just the beginning of the Window Expressions story. In future posts, this series will go into detail about how Windowing Functions work. We will explore how to configure partitioning, ordering and framing in your Window Functions. I’ll show you where you can use Window Functions in your queries and some techniques to bend the rules a bit in order to use Windowing Functions in creative ways to solve real-world business problems.
