WITH ROLLUP GRAND TOTAL AND SUBTOTAL

Question

I have a script that produces a result set that is almost there! I'm trying to get subtotals and grand totals. I get sub totals on the year column and a grand total at the end. My goal is to get the final result to state "grand total" instead of subtotal. Please note that my final row, 'location' also returns as null due to the rollup function.

SELECT
  YEAR,
  COUNT(ACCOUNTS) AS 'ACCOUNTS',        
  SUM(BALANCE) as 'BAL',
  LOCATION AS 'LOCATION'
FROM 
  ACCOUNT A 
WHERE C.CREATE BETWEEN 
  DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) 
  AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
GROUP BY 
  LOCATION, YEAR
WITH ROLLUP

result set...

YEAR  ACCOUNTS  BAL        LOCATION
----  --------  ---------  --------
NULL        11   80687.51  WA
NULL       107  592980.18  NULL

Desired result set...

YEAR          ACCOUNTS  BAL        LOCATION
----          --------  ---------  --------
sub total           11   80687.51  WA
grand total        107  592980.18  ALL

Solution 1

You can use GROUPING_ID to identify the grouping set each row is aggregating

SELECT
   CASE GROUPING_ID(LOCATION, YEAR)
     WHEN 0 THEN YEAR
     WHEN 2 THEN N'Sub total: ' + STR(YEAR) 
     WHEN 3 THEN N'Grand total'
  END
   COUNT(ACCOUNTS) AS 'ACCOUNTS',      
   SUM(BALANCE) as 'BAL',
   LOCATION AS 'LOCATION'
 FROM ACCOUNT A 
 WHERE C.CREATE BETWEEN DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) 
                    AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
 GROUP BY LOCATION, YEAR
 WITH ROLLUP

Solution 2

You need to use CASE WHEN (GROUPING(ColumnName) = 1), like this:

SELECT
  CASE WHEN GROUPING(YEAR) = 1 AND GROUPING(LOCATION) = 1 THEN 'grand total'
       WHEN GROUPING(YEAR) = 1 AND GROUPING(LOCATION) <> 1 THEN 'sub total'
       ELSE YEAR END AS YEAR
  COUNT(ACCOUNTS) AS 'ACCOUNTS',        
  SUM(BALANCE) as 'BAL',
  CASE WHEN GROUPING(LOCATION) = 1 THEN 'ALL' ELSE LOCATION AS 'LOCATION'
FROM 
  ACCOUNT A 
WHERE C.CREATE BETWEEN 
  DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()-1),0) 
  AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
GROUP BY 
  LOCATION, YEAR
WITH ROLLUP