Grand Totals and Subtotals in SQL Server

Just one of those things you may not know existed – the ROLLUP clause. It’s very useful for totaling. Are you used to doing something like this?

SELECT customer, sum(sales) from table
UNION
SELECT 'Total', sum(sales) from table

The problem here is that on larger datasets, this can really be a pain in terms of performance. ROLLUP can be extremely quick, but the syntax may not be straight forward. Here’s an example of summing sales in AdventureWorks2017 with a Grand Total.

USE AdventureWorks2017

SELECT
SalesOrderID,
sum(LineTotal)
FROM Sales.SalesOrderDetail
where SalesOrderID in (
	'43659',
	'43660',
	'43661',
	'43662',
	'43663'
)
GROUP BY SalesOrderID 
WITH ROLLUP

Notice I’ve added a WITH ROLLUP at the end of my GROUP BY. This creates the grand total. However, I’m left with a NULL for the SalesOrderID. To solve this I wouldn’t want to use a coalesce, because a NULL might be valid elsewhere. Instead we should use the GROUPING function.

USE AdventureWorks2017

SELECT
CASE
	WHEN GROUPING(SalesOrderID) = 1 THEN 'Total'
ELSE cast(SalesOrderID as nvarchar(10))
END as SalesOrderID,
sum(LineTotal)
FROM Sales.SalesOrderDetail
where SalesOrderID in (
	'43659',
	'43660',
	'43661',
	'43662',
	'43663'
)
GROUP BY SalesOrderID 
WITH ROLLUP

You can extend this even further into creating subtotals, but it’s gets extremely tricky to make sure all of your NULL’s are properly taken care of. Here’s an example utilizing the SalesOrderHeader and SalesOrderDetail tables.

USE AdventureWorks2017

SELECT
CASE
	WHEN GROUPING(soh.SalesOrderID) = 1 THEN 'Total'
ELSE cast(soh.SalesOrderID as nvarchar(10))
end as SalesOrderID,
CASE
	WHEN GROUPING(soh.SalesOrderID) = 0 AND GROUPING(CarrierTrackingNumber) = 1 then 'Sub-Total'
	WHEN GROUPING(soh.SalesOrderID) = 1 AND GROUPING(CarrierTrackingNumber) = 1 THEN 'Total'
ELSE cast(CarrierTrackingNumber as nvarchar(11))
end as CarrierTrackingNumber,
sum(LineTotal)
FROM [Sales].[SalesOrderHeader] soh
INNER JOIN Sales.SalesOrderDetail sod on soh.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID = '43659'
GROUP BY ROLLUP (soh.SalesOrderID, CarrierTrackingNumber)

Notice the WITH ROLLUP has become ROLLUP (<fields>) after the group by.