Use rollup to calculate grand totals with grouping

Question

I have a stored procedure:

ALTER PROCEDURE GetReportData
AS
BEGIN
    SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
        
        ----------- Processing
        SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing

    FROM 
        DBO.WorkingSession AS SS
    JOIN 
        DBO.Location AS LOC ON SS.LocationID = LOC.LocationID 
                            AND SS.BranchCode = LOC.BranchCode
    JOIN 
        DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY 
        LOC.SubCompanyNameVN, LOC.BranchName
    ORDER BY 
        LOC.SubCompanyNameVN
END

The result:

SubCompanyNameVN	BranchName	Total	CountNotProcessedYet	CountProcessing
Vùng 1	HNI_01	5	3	2
Vùng 1	HNI_02	15	5	10
Vùng 1	HNI_07	12	6	6
Vùng 2	HCM_01	86	50	36
Vùng 2	HCM_03	35	17	18

But now I expect my result to be:

SubCompanyOrBranchName	Total	CountNotProcessedYet	CountProcessing
Vùng 1	32	14	18
HNI_01	5	3	2
HNI_02	15	5	10
HNI_07	12	6	6
Vùng 2	121	67	54
HCM_01	86	50	36
HCM_03	35	17	18

How can I group the results by column SubCompanyNameVN (Group By SubCompanyNameVN) to calculate the total like the above table? I have researched and I think I can solve it with ROLLUP but I am confused about it.

Answer 1

As well as ROLLUP, you can also use GROUPING SETS, which gives you more flexibility in choosing the exact rollups you want.

It's better to use the GROUPING() function rather than ISNULL, because this tells you if the column was actually grouped, and you can see the difference between that an actual NULL

 SELECT 
     CASE WHEN GROUPING(LOC.BranchName) = 0
       THEN LOC.BranchName
       ELSE LOC.SubCompanyNameVN
       END AS SubCompanyOrBranchName,
     COUNT(LOC.BranchCode) as Total,

     ----------- Not Processed Yet
     SUM (CASE WHEN SS.Status IN (5, 6) THEN 1 ELSE 0 END) AS CountNotProcessedYet,
     
     ----------- Processing
     SUM (CASE WHEN SS.Status IN (3) THEN 1 ELSE 0 END) AS CountProcessing

 FROM 
     DBO.WorkingSession AS SS
 JOIN 
     DBO.Location AS LOC ON SS.LocationID = LOC.LocationID 
                         AND SS.BranchCode = LOC.BranchCode
 JOIN 
     DBO.Status AS ST ON SS.Status = ST.ID
 GROUP BY GROUPING SETS (
     (LOC.SubCompanyNameVN, LOC.BranchName),
     (LOC.SubCompanyNameVN)
 )
 ORDER BY 
     LOC.SubCompanyNameVN,
     GROUPING(LOC.BranchName) DESC, -- put the totalled rows first
     LOC.BranchName;