mohan111 mohan111 - 2 months ago 9
SQL Question

How to do Grouping Total by names wise in SQL SERVER

I have Table like this

DECLARE @T Table
(
EmployeDesc VARCHAR(20),
Empname Varchar(50),
headOffice INT,
SubOffice INT
);


Sample Data

INSERT INTO @T(EmployeDesc, Empname, headOffice, SubOffice) VALUES
('Sale','Rob',2,3),
('Discount','Rob',6,10),
('Sale','BOB',2,3),
('Discount','BOB',2,3);


How can we achieve this in SQL server

EmployeDesc Empname headOffice SubOffice

Sale Rob 2 3
Discount Rob 6 10
**Rob Total** 8 13
Sale BOB 2 3
Discount BOB 2 3
**BOB Total** 4 6


we can do in using
UNION ALL
or Running Totals but is there any other way to do this .

Can anyone suggest me

Answer

Use Grouping with ROLLUP:

;with X AS
(
    SELECT
        EmployeDesc, 
        Empname,
        SUM(headOffice) AS headOffice,
        SUM(SubOffice) AS SubOffice
    FROM @T
    GROUP BY Empname,EmployeDesc WITH ROLLUP
)
SELECT  
    CASE ISNULL(EmployeDesc,'') WHEN '' THEN '**'+Empname+ ' Total**' ELSE EmployeDesc END AS EmployeDesc, 
    CASE ISNULL(EmployeDesc,'') WHEN '' THEN '' ELSE Empname END AS Empname,    
    headOffice,
    SubOffice
FROM X
WHERE X.Empname IS NOT NULL