Skip to main content

GROUP BY clause

Often it is useful to group rows together before applying the COUNT, SUM, MIN, MAX and AVG functions. For example, the following SQL SELECT command will find the total salary bill for the whole company:

SELECT SUM(salary)

FROM employee;

However, if we wish to find the salary bill for each section in the company, the GROUP BY clause is used:

SELECT section_name, SUM(salary)

FROM employee

GROUP BY section_name;

The rows are grouped together by section name and the sum applied to each group.

Further examples are:

SQL command Result

SELECT section_name, MIN(salary), MAX(salary), AVG(salary)

FROM employee

GROUP BY section_name;

Calculate the minimum, maximum and average values of the salary in each section

SELECT gender, MIN(date_of_birth)

FROM employee

GROUP BY gender;

Find the date of births of the oldest male and female in the company

SELECT forename, surname, COUNT(*)

FROM employee

GROUP BY forename, surname;

Find how many employees have the same forename and surname

In the previous examples you might have noticed that the GROUP BY clause contains all the columns in the SELECT command, except the functions themselves.

Next: HAVING clause