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

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.


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


Thank you!
ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^