Hell everyone, I am going to share the basic
understanding of Rollup clause using SUM and GROUP BY clauses in SQL Server.
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 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..
|
