SIGN UP MEMBER LOGIN:
   
ARTICLE

CUBE ,ROLLUP and GROUP BY use in SQL Server

Posted by Sapna Malik Articles | SQL Server March 16, 2010
Tags: SQL Server
In this article,I will explain CUBE,ROLLUP and GROUP BY in SQL server.
Reader Level:

In this article,I will explain CUBE,ROLLUP and GROUP BY in SQL server.

In this article we will go over basic understanding of Rollup clause in SQL Server. ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator.

ROLLUP work with the "Group By " clause its main functioning comes into existance when we use Group by. We can get sub-total of row by using the Rollup funtion.When result is return by Group By class first row display the grand total.

First of all,I will create one table for example:-

Use CeilInn3;

GO

 

CREATE TABLE Tbl_Population (

Country VARCHAR(50),

[State] VARCHAR(50),

City VARCHAR(50),

[Population (in Millions)] INT

)

 

INSERT INTO Tbl_Population VALUES('India', 'Delhi','East Delhi',10 )

INSERT INTO Tbl_Population VALUES('India', 'Delhi','West Delhi',8.5 )

INSERT INTO Tbl_Population VALUES('India', 'Delhi','North Delhi',7.5)

INSERT INTO Tbl_Population VALUES('India', 'Delhi','South Delhi',8.5)

INSERT INTO Tbl_Population VALUES('India', 'Karnataka','Bangalore',10.5)

INSERT INTO Tbl_Population VALUES('India', 'Karnataka','Belur',4.5)

INSERT INTO Tbl_Population VALUES('India', 'Karnataka','Manipal',3.5)

INSERT INTO Tbl_Population VALUES('India', 'Maharastra','Mumbai',35)

INSERT INTO Tbl_Population VALUES('India', 'Maharastra','Pune',25)

INSERT INTO Tbl_Population VALUES('India', 'Maharastra','Nagpur',17 )

INSERT INTO Tbl_Population VALUES('India', 'Maharastra','Nashik',16.5)

select * from Tbl_Population;

 

GO

Output:-

Rollup2.bmp

Now, we need to create a report on population at 3 levels: City, State and Country.

ROLLUP Operation:-

ROLLUP Operation  generates a result set that represents aggregates for a hierarchy of values in the selected columns. It's an extension to GROUP BY clause.  

Example of RollUp Operation:-

use CeilInn3;

GO

 

SELECT Country,[State],City,

SUM ([Population (in Millions)]) AS [Population (in Millions)]

FROM Tbl_Population

GROUP BY Country,[State],City WITH ROLLUP

 

GO

However, SQL Server provides a very easy solution. Just add the WITH ROLLUP clause in GROUP BY and you get the desired results.

Output:-

Rollup1.bmp

CUBE Operation:-

CUBE Operation  generates a result set that represents aggregates for all combinations of values in the selected columns. It's an additional switch to GROUP BY clause.  

Example of CUBE Operation:-

use CeilInn3;

GO

 

SELECT Country,[State],

SUM ([Population (in Millions)]) AS [Population (in Millions)]

FROM Tbl_Population

GROUP BY Country,[State] WITH CUBE

 

GO

Output:-

 RollUp4.bmp

 

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING