Pages

Thursday, May 8, 2014

Some Useful Aggregation (WITH ROLLUP/WITH CUBE/GROUPING SETS)

Introduction

In this article I am trying to provide some example related to aggregation function.

WITH ROLLUP

WITH ROLLUP allows us to do the multiple level of aggregation within a single SQL statement. To understand it, I directly jumped to an example.
Create and Insert Records in a Table Objects

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE',N'U') IS NOT NULL
   BEGIN
     DROP TABLE [dbo].[tbl_EMPLOYEE];
   END
GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]
       (EMPID        INT   NOT NULL,
        EMPSALYEAR   INT   NOT NULL,
        EMPSAL       INT   NOT NULL);
GO

INSERT INTO  [dbo].[tbl_EMPLOYEE]
       (EMPID, EMPSALYEAR, EMPSAL)
VALUES (101, 2012, 2000),
       (101, 2013, 3000),
       (101, 2014, 4000),
       (102, 2012, 5000),
       (102, 2013, 5500),
       (102, 2014, 6000),
       (103, 2012, 1000),
       (103, 2013, 3000),
       (103, 2014, 7000);                  

SELECT * FROM [dbo].[tbl_EMPLOYEE]
GO

EMPID       EMPSALYEAR  EMPSAL
----------- ----------- -----------
101         2012        2000
101         2013        3000
101         2014        4000
102         2012        5000
102         2013        5500
102         2014        6000
103         2012        1000
103         2013        3000
103         2014        7000

Now we use Group By in it

SELECT EMPSALYEAR, SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY EMPSALYEAR;

GO

EMPSALYEAR  EMPSAL
----------- -----------
2012        8000
2013        11500
2014        17000

Now we want a GRAND TOTAL at the end.
We can do it like this

SELECT CONVERT(VARCHAR, EMPSALYEAR) EMPSALYEAR,
       SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY EMPSALYEAR
UNION ALL
SELECT 'GRAND TOTAL' EMPSALYEAR,
       SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GO

EMPSALYEAR                     EMPSAL
------------------------------ -----------
2012                           8000
2013                           11500
2014                           17000
GRAND TOTAL                    36500

But it is performance overhead for SQL Server.
We can do it easily by WITH ROLLUP

SELECT EMPSALYEAR, SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY EMPSALYEAR
WITH ROLLUP
GO

EMPSALYEAR                     EMPSAL
------------------------------ -----------
2012                           8000
2013                           11500
2014                           17000
NULL                           36500

By using little bit formatting

SELECT CASE WHEN GROUPING(EMPSALYEAR) = 0
            THEN CONVERT(VARCHAR, EMPSALYEAR)
            ELSE 'GRAND TOTAL'
       END AS EMPSALYEAR, SUM(EMPSAL) AS Sales
FROM [dbo].[tbl_EMPLOYEE]
GROUP BY EMPSALYEAR WITH ROLLUP;

EMPSALYEAR                     Sales
------------------------------ -----------
2012                           8000
2013                           11500
2014                           17000
GRAND TOTAL                    36500

WITH CUBE

The WITH CUBE clause gives us to compute multiple levels of aggregation in a single statement.

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL
FROM [dbo].[tbl_EMPLOYEE]
GROUP BY EMPID, EMPSALYEAR WITH CUBE
GO

EMPID       EMPSALYEAR  EMPSAL
----------- ----------- -----------
101         2012        2000
102         2012        5000
103         2012        1000
NULL        2012        8000
101         2013        3000
102         2013        5500
103         2013        3000
NULL        2013        11500
101         2014        4000
102         2014        6000
103         2014        7000
NULL        2014        17000
NULL        NULL        36500
101         NULL        9000
102         NULL        16500
103         NULL        11000

New Syntax of WITH ROLLUP and WITH CUBE

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS Sales
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY ROLLUP(EMPID, EMPSALYEAR)
GO

EMPID       EMPSALYEAR  Sales
----------- ----------- -----------
101         2012        2000
101         2013        3000
101         2014        4000
101         NULL        9000
102         2012        5000
102         2013        5500
102         2014        6000
102         NULL        16500
103         2012        1000
103         2013        3000
103         2014        7000
103         NULL        11000
NULL        NULL        36500



SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS Sales
FROM  [dbo].[tbl_EMPLOYEE]
GROUP BY CUBE(EMPID, EMPSALYEAR)
GO

EMPID       EMPSALYEAR  Sales
----------- ----------- -----------
101         2012        2000
102         2012        5000
103         2012        1000
NULL        2012        8000
101         2013        3000
102         2013        5500
103         2013        3000
NULL        2013        11500
101         2014        4000
102         2014        6000
103         2014        7000
NULL        2014        17000
NULL        NULL        36500
101         NULL        9000
102         NULL        16500
103         NULL        11000

GROUPING SETS

The GROUPING SETS syntax is more powerful.  It allows us to specify precisely which aggregations we want to compute.

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY GROUPING SETS((EMPID, EMPSALYEAR), (EMPID), ())
GO

EMPID       EMPSALYEAR  EMPSAL
----------- ----------- -----------
101         2012        2000
101         2013        3000
101         2014        4000
101         NULL        9000
102         2012        5000
102         2013        5500
102         2014        6000
102         NULL        16500
103         2012        1000
103         2013        3000
103         2014        7000
103         NULL        11000
NULL        NULL        36500

SELECT EMPID, EMPSALYEAR, SUM(EMPSAL) AS EMPSAL
FROM   [dbo].[tbl_EMPLOYEE]
GROUP BY GROUPING SETS((EMPID, EMPSALYEAR), (EMPID),(EMPSALYEAR), ())
GO

EMPID       EMPSALYEAR  EMPSAL
----------- ----------- -----------
101         2012        2000
102         2012        5000
103         2012        1000
NULL        2012        8000
101         2013        3000
102         2013        5500
103         2013        3000
NULL        2013        11500
101         2014        4000
102         2014        6000
103         2014        7000
NULL        2014        17000
NULL        NULL        36500
101         NULL        9000
102         NULL        16500
103         NULL        11000



Hope you like it.

No comments:

Post a Comment