Select Distinct Logo Clear Background

Business Analytics Blog

Group by ROLLUP in SQL Server

How to use ROLLUP in SQL Server

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

a date dimension table

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

Basic example of group by rollup in sql server

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])

 

Group by Rollup in sql server with labelled totals

 

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

Group by Rollup in sql server with labelled totals

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 a sub total

 

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!

Business Analytics Blog

 

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert