python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
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

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions