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:

But now I expect my result to be:

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.

CodePudding user response：

Use cte and union all with group by

With cte AS ( 
     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,
        ROW_NUMBER() OVER(PARTITION BY LOC.SubCompanyNameVN ORDER BY LOC.SubCompanyNameVN) AS seq

    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)
        
select SubCompanyNameVN,Total,CountNotProcessedYet,CountProcessing
from
(select SubCompanyNameVN,
       sum(Total) As Total,
       sum(CountNotProcessedYet) As CountNotProcessedYet,
       sum(CountProcessing) As CountProcessing,
       dense_rank()over(order by SubCompanyNameVN) As rnk
   from cte
   group by SubCompanyNameVN
   union all
   select BranchName,
       Total,
       CountNotProcessedYet,
       CountProcessing,
       dense_rank()over(order by SubCompanyNameVN) As rnk
   from cte) T
order by rnk,SubCompanyNameVN desc

Demo in db<>fiddle

CodePudding user response：

I found the solution finally by using ROLLUP, here is what I need:

SELECT 
        (CASE 
            WHEN
                BranchName is NULL
                    THEN SubCompanyNameVN ELSE BranchName
        END) AS SubCompanyOrBranchName,
        Total, CountNotProcessedYet, PercentNotProcessedYet, CountProcessing, PercentProcessing,
        CountProcessedIn5Days, PercentProcessedIn5Days, CountProcessedOver5Days, PercentProcessedOver5Days
    FROM
    (
        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 ROLLUP(LOC.SubCompanyNameVN, LOC.BranchName)
        ORDER BY LOC.SubCompanyNameVN, LOC.BranchName OFFSET 1 ROWS
    ) T

CodePudding user response：

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;