SQL SERVER – Introduction of Rollup clause using SUM and GROUP BY clause

SQL SERVER – Introduction of Rollup clause using SUM and GROUP BY clause.

Hell everyone, I am going to share the basic understanding of Rollup clause using SUM and GROUP BY clauses in SQL Server.

The Rollup clause is used to handle the aggregate operation on multiple levels in hierarchy.

A Rollup operation can calculate the equivalent of an OLAP dimension or hierarchy.

The Query for the same to understand in detail how is work.

--CREATE TEMP TABLE FOR TESTING ROLLUP CLAUSE

CREATE TABLE #POPULATIONS (

            ID INT IDENTITY(1,1),

            COUNTRYNAME NVARCHAR(255),

            STATENAME NVARCHAR(255),

            CITYNAME NVARCHAR(255),

            POPULATIONS BIGINT,

            CREATEDDATE DATETIME

)

GO

--INSERT QUERY FOR INSERT ROWS

INSERT INTO #POPULATIONS VALUES('India', 'U.P.', 'East U.P.',4.1,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'U.P.', 'South U.P.',5.9,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'U.P.', 'North U.P.',3,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'U.P.', 'West U.P.',3.5,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'Bihar', 'East Bihar',5,GETDATE() )

INSERT INTO #POPULATIONS VALUES('India', 'Bihar', 'South Bihar',6,GETDATE() )

INSERT INTO #POPULATIONS VALUES('India', 'Bihar', 'North Bihar',8,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'Bihar', 'West Bihar',9,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'Delhi', 'East Delhi',10,GETDATE() )

INSERT INTO #POPULATIONS VALUES('India', 'Delhi', 'South Delhi',8,GETDATE() )

INSERT INTO #POPULATIONS VALUES('India', 'Delhi', 'North Delhi',5,GETDATE())

INSERT INTO #POPULATIONS VALUES('India', 'Delhi', 'West Delhi',9,GETDATE())

--USE OF ROLLUP AND GROUP BY

SELECT COUNTRYNAME, STATENAME, CITYNAME, SUM(POPULATIONS) AS POPULATIONS

            FROM #POPULATIONS

                        GROUP BY COUNTRYNAME, STATENAME, CITYNAME WITH ROLLUP

DROP TABLE #POPULATIONS

 The Query result as given below.

For more detail you can go below link

http://www.codefari.com/2015/10/how-to-use-rollup-with-group-by-clause.html

http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/

Thank you!