/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Wednesday, May 21, 2014

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;





No comments:

Post a Comment

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago