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)
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY EmpId, Yr WITH ROLLUP
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY EmpId, Yr WITH CUBE
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 1 2006 18000.00 1 2007 25000.00 1 NULL 55000.00 2 2005 15000.00 2 2006 6000.00 2 NULL 21000.00 3 2006 20000.00 3 2007 24000.00 3 NULL 44000.00 NULL NULL 120000.00
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 2 2005 15000.00 NULL 2005 27000.00 1 2006 18000.00 2 2006 6000.00 3 2006 20000.00 NULL 2006 44000.00 1 2007 25000.00 3 2007 24000.00 NULL 2007 49000.00 NULL NULL 120000.00 1 NULL 55000.00 2 NULL 21000.00 3 NULL 44000.00
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 1 2006 18000.00 1 2007 25000.00 1 NULL 55000.00 2 2005 15000.00 2 2006 6000.00 2 NULL 21000.00 3 2006 20000.00 3 2007 24000.00 3 NULL 44000.00
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr), ())
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 1 2006 18000.00 1 2007 25000.00 2 2005 15000.00 2 2006 6000.00 3 2006 20000.00 3 2007 24000.00 NULL NULL 120000.00
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId), (Yr))
EmpId Yr Sales ----------- ----------- --------------------- NULL 2005 27000.00 NULL 2006 44000.00 NULL 2007 49000.00 1 NULL 55000.00 2 NULL 21000.00 3 NULL 44000.00
SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr))
EmpId Yr Sales ----------- ----------- --------------------- 1 2005 12000.00 2 2005 15000.00 1 2006 18000.00 2 2006 6000.00 3 2006 20000.00 1 2007 25000.00 3 2007 24000.00