Ardalan Shahgholi Ardalan Shahgholi - 29 days ago 8
SQL Question

Using RollUp and Group By in SQL Server?

I have table

Sales
in
SQL Server 2012


Use tempdb
Go

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)


I want create a report with this results :

/*

EmpId------ Yr----- SUM(Sales) BY EmpId, Yr---------- SUM(Sales) BY EmpId ----------SUM(Sales)

1 2005 12000.00 12000.00 12000.00
1 2006 18000.00 30000.00 30000.00
1 2007 25000.00 55000.00 55000.00
1 NULL 55000.00 55000.00
2 2005 15000.00 15000.00 70000.00
2 2006 6000.00 21000.00 76000.00
2 NULL 21000.00 76000.00
3 2006 20000.00 20000.00 96000.00
3 2007 24000.00 44000.00 120000.00
3 NULL 44000.00 120000.00
NULL NULL 120000.00
*/


I write a query like this :

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP


How can I change my query for get more columns like abauve.

Answer

In SQL Server 2012+, you can do cumulative sums using window functions. The following basically does what you want:

SELECT EmpId, Yr, SUM(Sales) AS Sales,
       SUM(case when Yr is not null then SUM(Sales) end) OVER
               (PARTITION BY EmpId
                Order By (case when Yr is null then 0 else 1 end) desc, Yr
               ),
       SUM(case when yr is not null then SUM(SALES) end) OVER
               (Order by EmpId, (case when Yr is null then 0 else 1 end) desc, Yr)
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
ORDER BY (case when EmpId is null then 0 else 1 end) desc, empid,
         (case when Yr is null then 0 else 1 end) desc, yr;

This is tricky because the interplay between the rollup and the window functions requires care.

Here is the SQL Fiddle.

EDIT:

To fix the very last cell on the last row, you can add a case statement:

SELECT EmpId, Yr, SUM(Sales) AS Sales,
       SUM(case when Yr is not null then SUM(Sales) end) OVER
               (PARTITION BY EmpId
                Order By (case when Yr is null then 0 else 1 end) desc, Yr
               ),
       (case when yr is null and empid is null
             then sum(case when yr is not null and empid is not null then sum(sales) end) over ()
             else SUM(case when yr is not null then SUM(SALES) end) OVER
                      (Order by EmpId, (case when Yr is null then 0 else 1 end) desc, Yr)
        end)
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
ORDER BY (case when EmpId is null then 0 else 1 end) desc, empid,
         (case when Yr is null then 0 else 1 end) desc, yr;