Using RollUp and Group By in SQL Server?

Question

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 1

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;