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.
Posted by: MR.JOYDEEP DAS
Thank You Sir.....
ReplyDeleteExcellent discussion on aggregation.
It will be very helpful to me.