Grouping sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

Grouping Sets in sql server 2008 GROUPING SETS, CUBE, and ROLLUP GROUPING_ID function

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATETIME   NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20060802', 3, 'A', 10), (10001, '20061224', 1, 'A', 12),
  (10005, '20061224', 1, 'B', 20), (40001, '20070109', 4, 'A', 40),
  (10006, '20070118', 1, 'C', 14), (20001, '20070212', 2, 'B', 12),
  (40005, '20080212', 4, 'A', 10), (20002, '20080216', 2, 'C', 20),
  (30003, '20080418', 3, 'B', 15), (30004, '20060418', 3, 'C', 22),
  (30007, '20060907', 3, 'D', 30);

select * from Orders

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
  ( custid, empid, YEAR(orderdate) ),
  ( custid, YEAR(orderdate)        ),
  ( empid, YEAR(orderdate)         ),
  () );

--CUBE produces 2^n grouping sets.
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty FROM dbo.Orders
GROUP BY cube(custid,empid,orderdate)

--The ROLLUP subclause produces only the grouping sets that have business value
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty FROM dbo.Orders
GROUP BY ROLLUP(custid,empid,orderdate)

--Using GROUPING_ID() function
SELECT 
  GROUPING_ID(
    custid, empid,
    YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ) AS grp_id,
  custid, empid,
  YEAR(orderdate) AS orderyear,
  MONTH(orderdate) AS ordermonth,
  DAY(orderdate) AS orderday,
  SUM(qty) AS qty
FROM dbo.Orders
GROUP BY
  CUBE(custid, empid),
  ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate))
  order by grp_id;