Introduction to ROLLUP in SQL Server
Did you know that there is a simple way to add roll up sub totals in SQL Server?
SQL ROLLUP is an extension of the GROUP BY clause in SQL, providing a way to generate subtotals and grand totals for multiple dimensions within a dataset. It enables analysts to explore data at different levels of granularity, making it easier to identify patterns, trends, and relationships.
Understanding the Syntax of ROLLUP in SQL Server
The best place to start to understand the rollup function is with a simple group by example
In this example we are using a date dimension table to return the number of days per calendar month
SELECT [Year], [quarter], [monthname], count([date]) as [no of days] FROM [dbo].[DimDate] WHERE [Year] = 2022 GROUP BY [Year], [quarter], [monthname]
this gives us the standard results for each month as follows
Adding the ROLLUP extension to the group by clause
GROUP BY ROLLUP ([Year], [quarter], [monthname])
the query now inserts sub total rows for each year, quarter and monthname
but, it does not provide labels for these subtotals, instead these have a null value
by using a coalesce function, we can easily add a relevant label
SELECT coalesce ([Year],’Total All Years’) as [Year]
, coalesce ([quarter],’Total All Quarters’) as [Quarter]
, coalesce ([monthname], ‘Total All Months’) as [Month]
, count([date]) as [no of days]
FROM [dbo].[DimDate]
WHERE [Year] = 2022
GROUP BY ROLLUP ([Year], [quarter], [monthname])
Using Partial rollups
You may want to avoid having subtotals in every column, with partial rollups you can specify which columns you want to have the rollups
In this example we just select the rollup values into the quarter and months
SELECT coalesce ([Year],’Total All Years’) as [Year]
, coalesce ([quarter],’Total All Quarters’) as [Quarter]
, coalesce ([monthname], ‘Total All Months’) as [Month]
, count([date]) as [no of days]
FROM [dbo].[DimDate]
WHERE [Year] = 2022
GROUP BY [Year], ROLLUP ([quarter], [monthname])
Typical use cases
The flexibility of this feature means that it can be used for any hierarchical data set to instantly add useful subtotals without the need to write seperate sub queries or use window functions
A great example of this could be to look at retail sales, which contain products, product groups, stores and regions, automatically inserting these roll ups can be a real timesaver and make your queries easier to follow
Just be careful not to double count these subtotals in any subsequent reporting which may just sum column totals
if you took the sum of the number of days from the example below you would get 1460 days (three times the total), this is because it would include the subtotals for the months, plus the quarters, plus the total of the 4 quarters and the total for the year
Conclusion
Rollup in SQL server is a really powerful yet simple way to add sub totals to your queries and the flexibility to use partial rollups allows you more control over when to use them
You can also use this with other types of aggregation functions such as SUM, COUNT, AVG, MAX, MIN
give it a try then next time you need to add sub totals
Subscribe to our channel to see more tips and timesavers
Select Distinct YouTube Channel
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
