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.