• Marek Vavrovic

Grouping in SQL Server


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



7 views0 comments

Recent Posts

See All