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

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book