Aggregating Data in SQL

15 September 2016
15 Sep 2016
1 min read

A common problem I run into while writing SQL code is the idea of rolling up data to a higher level. This just means taking a set of data, and making it less granular in some way. This is commonly done to speed up calculations, at the expense of the ability to drill down into that data. A common example is taking day level data and turning it into month level data. Here, obviously you lose the ability to drill down to sales per day (for example), but it can potentially speed up SQL procedures tremendously.

The way I do that currently in Microsoft SQL is by using the dateadd(datediff()) method, along with a group by. Say you have a table with Sales, Product, and day level date, and you want to turn that into month level data. So, you’d do something that looks like this:


That will take your data, sum the sales per product per month, and group it by product and month. Definitely useful, although I will be waiting for the day until this is built in.

Want to know more?

I spend a ton of time thinking on how to work smarter, not harder. If you'd like to be the first to know what I'm thinking about, sign up to the list below.

Clearing the console and environment in R

O(n) for data scientists