SQL Server GROUP BY clause
SQL Server GROUPING SETS
SQL Grouping Function
1. SQL Server GROUP BY clause
The GROUP BY clause allows you to arrange the rows of a query in groups. The groups are determined by the columns that you specify in the GROUP BY clause.
Create Table Emp
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int,
Country nvarchar(10)
)
Go
Insert Into Emp Values (1, 'Harry', 'Male', 5000, 'USA')
Insert Into Emp Values (2, 'Ralf', 'Male', 4500, 'Germany')
Insert Into Emp Values (3, 'Britney', 'Female', 5500, 'USA')
Insert Into Emp Values (4, 'Sara', 'Female', 4000, 'Germany')
Insert Into Emp Values (5, 'Matheus', 'Male', 3500, 'Germany')
Insert Into Emp Values (6, 'Jade', 'Female', 5000, 'UK')
Insert Into Emp Values (7, 'John', 'Male', 6500, 'UK')
Insert Into Emp Values (8, 'Elizabeth', 'Female', 7000, 'USA')
Insert Into Emp Values (9, 'Tom', 'Male', 5500, 'UK')
Go
Example 1.1: calculate sum of salary by country and gender
Select Country, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Country, Gender
Example 1.2: calculate sum of salary by country and gender. Calculate grand total for countries.
Select Country, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Country, Gender
UNION all
Select Country, NULL, Sum(Salary) as TotalSalary
From Emp
Group By Country
...rows 7,8,9 represents the grand total for countries
Example 1.3: calculate sum of salary by country and gender. Calculate grand total for countries and genders.
Select Country, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Country, Gender
UNION ALL
Select Country, NULL, Sum(Salary) as TotalSalary
From Emp
Group By Country
UNION ALL
Select NULL, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Gender
Example 1.4: calculate sum of salary by country and gender. Calculate grand total by country, gender and salary.
Select Country, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Country, Gender
UNION ALL
Select Country, NULL, Sum(Salary) as TotalSalary
From Emp
Group By Country
UNION ALL
Select NULL, Gender, Sum(Salary) as TotalSalary
From Emp
Group By Gender
UNION ALL
Select NULL, NULL, Sum(Salary) as TotalSalary
From Emp
2. SQL Server GROUPING SETS
The GROUPING SETS is an option of the GROUP BY clause. The GROUPING SETS defines multiple grouping sets within the same query.
The following illustrates the general syntax of the GROUPING SETS option:
SELECT c1, c2,
aggregate (c3)
FROM table
GROUP BY GROUPINGSETS (
(c1, c2),
(c1),
(c2),
() );
Example 2.1
Select Country, Gender,
Sum(Salary) TotalSalary
From Emp
Group BY
GROUPING SETS
(
(Country, Gender),
(Country),
(Gender) ,
()
)
Order By Grouping(Country),
Grouping(Gender),
Gender
Cube and Rollup clause
CUBE : generates a result set that shows aggregates for all combinations of values in the selected columns. Gives us all possible conbinations.
ROLLUP: generates a result set that shows aggregates for a hierarchy of values in the selected columns. Gives us the combination in hierarchie [grant total].
Syntax for CUBE:
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Emp
GROUP BY Cube (Country,Gender)
--OR
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Emp
GROUP BY Country, Gender with Cube
Syntax for ROLLUP:
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Emp
GROUP BY Country, Gender with rollup
--OR
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Emp
GROUP BY ROLLUP (Country,Gender)
3. SQL Grouping Function
The Grouping in SQL Server returns whether the grouping on the defined column has happened or not, by returning 0 and 1. In this grouping function example, we will show you the same.
Example 3.1
SELECT Country,
Gender,
SUM(Salary) AS TotalSalary,
GROUPING(Country) 'GRP. in Country',
GROUPING(Gender) 'GRP. in Gender'
FROM Emp
GROUP BY Country, Gender with rollup
