Cube, Rollup & Grouping Sets In SQL Server

Cube And Rollup In SQL Server

Cube In SQL

• CUBE is an aggregate operator that produces a super-aggregate row.
• The summary row is displayed for every possible combination of groups in the result set.
• In addition to the usual rows provided by the GROUP BY, it also provides the summary of the rows that the GROUP BY clause generates.
• The summary row displays NULL in the result set but at the same time returns all the values for those.
• Cube() in SQL server produces the result set by generating all combinations of columns specified in GROUP BY CUBE().
• Write a query to retrieve Sum of Salary grouped by all possible combinations of the 2 columns (City and Gender) as well as GRAND TOTAL. – Query Given Below In this Blog

Rollup In SQL Server

• In addition to the usual rows that are generated by the GROUP BY clause, it also introduces summary rows into the result set.
• It arranges the groups from the lowest to the highest.
• It is similar to CUBE operator but generates a result set that shows groups arranged in a hierarchical order.
• Group hierarchy in the result is dependent on the order in which the columns that are grouped are specified.

SQL Query For Cube & Rollup Commands

-- CREATING EMPLOYEE TABLE
create table Employee_Tbl 
(
Id int primary key identity,
Name varchar(50),
Gender varchar(50),
City varchar(50),
Salary int
);

-- INSERTING DATA INTO EMPLOYEE TABLE
insert into Employee_Tbl values
('Ali','Male','Karachi',15000),
('Anas','Male','Hyderabad',12000),
('Afshan','Female','Karachi',14000),
('Imran','Male','Hyderabad',11000),
('Anum','Female','Hyderabad',13000),
('Usman','Male','Sukkur',11000),
('Sumaira','Female','Sukkur',16000),
('Amir','Female','Hyderabad',17000),
('Zain','Male','Sukkur',12000),
('Maryam','Female','Karachi',15000);

select * from Employee_Tbl;

-- EXAMPLE OF GROUP BY COMMAND WITH SINGLE COLUMN
select City, sum(Salary) as Total_Salary
from Employee_Tbl
group by City;

-- EXAMPLE OF GROUP BY COMMAND WITH MULTIPLE COLUMN
select City,Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by City,Gender;

-- EXAMPLE OF CUBE COMMAND
select City,Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by cube(City,Gender);

-- ANOTHER EXAMPLE OF CUBE COMMAND
select City,Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by City,Gender with cube;

-- EXAMPLE OF ROLLUP COMMAND
select City,Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by City,Gender with rollup;

-- ANOTHER EXAMPLE OF ROLLUP COMMAND
select City,Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by rollup(City,Gender);

-- ANOTHER EXAMPLE OF CUBE COMMAND
select City, sum(Salary) as Total_Salary
from Employee_Tbl
group by cube(City);

-- ANOTHER EXAMPLE OF ROLLUP COMMAND
select City, sum(Salary) as Total_Salary
from Employee_Tbl
group by rollup(City);

Grouping Sets In SQL Server

• The GROUPING SETS operator allows you to group together multiple groupings of columns followed by an optional grand total row, denoted by parentheses, ().
• It is more efficient to use GROUPING SETS operators instead of multiple GROUP BY with UNION clauses because the latter adds more processing overheads on the database server.

SQL Query For Grouping Sets

-- CREATING EMPLOYEE TABLE
create table Employee_Tbl 
(
Id int primary key identity,
Name varchar(50),
Gender varchar(50),
City varchar(50),
Salary int
);

-- INSERTING DATA IN EMPLOYEE TABLE
insert into Employee_Tbl values
('Ali','Male','Karachi',15000),
('Anas','Male','Hyderabad',12000),
('Afshan','Female','Karachi',14000),
('Imran','Male','Hyderabad',11000),
('Anum','Female','Hyderabad',13000),
('Usman','Male','Sukkur',11000),
('Sumaira','Female','Sukkur',16000),
('Amir','Female','Hyderabad',17000),
('Zain','Male','Sukkur',12000),
('Maryam','Female','Karachi',15000);

select * from Employee_Tbl;

-- EXAMPLE OF GROUPING SETS IN SQL
select City, Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by
grouping sets
(
(City,Gender),
(City),
(Gender),
()
)
order by grouping(City),grouping(Gender);


-- EXAMPLE OF GROUP BY IN SQL
select City, Gender, sum(Salary) as Total_Salary
from Employee_Tbl
group by City,Gender

union all

select City, NULL as [Male / Female], sum(Salary) as Total_Salary
from Employee_Tbl
group by City

union all

select NULL, NULL as [Male / Female], sum(Salary) as Total_Salary
from Employee_Tbl
group by ()

union all

select NULL, Gender as [Male / Female], sum(Salary) as Total_Salary
from Employee_Tbl
group by Gender