Group by clause

Group by clause

Group by clause is used in conjuction with select statement to retrive the data into a groups.

Group by clause follows the where clause in select statement and precedes the order by clause.

select * from Employees

select TitleOfCourtesy , COUNT(EmployeeID)  from Employees

group by TitleOfCourtesy

select TitleOfCourtesy ,BirthDate,COUNT(EmployeeID) as cnt  from Employees

group by TitleOfCourtesy,BirthDate

select TitleOfCourtesy ,BirthDate from Employees

group by TitleOfCourtesy,BirthDate

select TitleOfCourtesy ,BirthDate,COUNT(EmployeeID) as cnt  from Employees

group by BirthDate,TitleOfCourtesy

select TitleOfCourtesy ,BirthDate,COUNT(EmployeeID) as cnt  from Employees

group by TitleOfCourtesy,BirthDate

Hierarchy of clauses

SELECT

<COLUMN LIST>, <GROUP BY FUNCTIONS>

FROM <TABLE_NAME>

WHERE <FILTER CONDITIONS>

GROUP BY <COLUMN_LIST>

HAVING <CONDITION BASED ON GROUP BY FUNCTIONS>

select TitleOfCourtesy ,BirthDate,COUNT(EmployeeID) as cnt, EmployeeID 

from Employees

group by TitleOfCourtesy,BirthDate, EmployeeID

having EmployeeID <>0

order by EmployeeID

All the column besides aggregate function clause must be included in the group by clause

Column aliase name not included into the group by clause

Group by clause can be used in aggregate functions like Min, Max, Count, Sum, AVG