Group by – grouping sets, roll up & cube
This article discusses Grouping Sets in T-SQL. Grouping Sets is a new feature in T-SQL in SQL Server 2008.The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause.By using GROUPING SETS() we can specify multiple groupings in a single query. GROUPING SETS() generates the result by producing a UNION ALL set of the result sets generated by specified grouping sets.
GROUPING SETS():-
Grouping Sets will allow multiple groupings to be returned in one record set.
The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.We can also use GROUPING SETS in replacement of ROLLUP and CUBE.
CUBE:-
Generates a result set that shows aggregates for all combinations of values in the selected columns.
ROLLUP:-
Generates a result set that shows aggregates for a hierarchy of values in the selected columns.
For example, a simple table Employee contains the following:SELECT * FROM Employee
GROUPING SETS:-
Grouping Sets will allow multiple groupings to be returned in one record set.
GROUPING SETS also allows for "Grand total","Sub Total" data for the entire set of data, or just for sections of aggregations.
SELECT Region, avg(Salary) Average_Salary
from Employee
Group BY
GROUPING SETS
(
(Region)
)
You can see that the result set contains rows grouped by region.
Adding another column into the Grouping Set
SELECT Region, Department, avg(Salary) Average_Salary
from Employee
Group BY
GROUPING SETS
(
(Region),
(Department)
)
You can see that the result set contains rows grouped by region and department individually.
Before SQL Server 2008, to do this we must write a different query and UNION these queries.
SELECT Region, NULL, avg(Salary) Average_Salary from Employee
group by Region
UNION ALL
SELECT NULL, Department, avg(Salary) Average_Salary from Employee
group by Department
UNION ALL
SELECT NULL, NULL, avg(Salary) Average_Salary from Employee
Output of the above query will be:
The same result set can be gained by using Grouping Sets as shown in the below query:
SELECT Region, Department, avg(Salary) Average_Salary
from Employee
Group BY
GROUPING SETS
(
(Region),
(Department),
()
)
You can see that the result set contains rows grouped by each set in the specified Grouping Sets. You can see the average salary of employees for each region and department.
So we can be achieved simply by using grouping sets instead of multiple joins
Another Example:-
SELECT Region, Department, avg(Salary) Average_Salary
from Employee
Group BY
GROUPING SETS
(
(Region, Department),
(Region),
(Department) ,
()
)
This will generate 4 sets of totals:
A grand total of all rows
For each Region,
For each Department,
For each Region/Department
ROLLUP:-
ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
SELECT Region,Department,avg(Salary) Average_Salary
FROM Employee
GROUP BY Region,Department WITH ROLLUP
CUBE:-
CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
SELECT Region,Department,avg(Salary) Average_Salary
FROM Employee
GROUP BY Region,Department WITH cube
Using GROUPING SETS, we can write multiple "Group By" clauses within a single query and get a single result set. Also it can be used as equivalent to as well as with ROLLUP and CUBE.