GROUP BY and HAVING Clauses - SELECT Statement

The GROUP BY clause defines one or more columns as a group such that all rows within any group have the same values for those columns. Example 6.22 shows the simple use of the GROUP BY clause in a SELECT statement.

Example 6.22

Get all jobs of the employees:

USE sample;
SELECT job
	FROM works_on
	GROUP BY job;Code language: PHP (php)

The result is

job
NULL
Analyst
Clerk
Manager

In Example 6.22, the GROUP BY clause builds different groups for all possible values (NULL, too) appearing in the job column.

Note – There is a restriction regarding the use of columns in the GROUP BY clause. Each column appearing in the SELECT list of the query must also appear in the GROUP BY clause. This restriction does not apply to constants and to columns that are part of an aggregate function. (Aggregate functions are explained in the next subsection.) This makes sense, because only columns in the GROUP BY clause are guaranteed to have a single value for each group.

A table can be grouped by any combination of its columns. Example 6.23 shows the grouping of rows of the works_on table using two columns.

Example 6.23

Group all employees using their project numbers and jobs:

USE sample;
SELECT project_no, job
	FROM works_on
	GROUP BY project_no, job;Code language: PHP (php)

The result is

project_no	job
p1	Analyst
p1	Clerk
p1	Manager
p1	NULL
p2	NULL
p2	Clerk
p3	Analyst
p3	Clerk
p3	Manager

The result of Example 6.23 shows that there are nine groups with different combinations of project numbers and jobs. The only two groups that contain more than one row are

p2	Clerk	25348, 28559
p2	NULL	18316, 29346

The sequence of the column names in the GROUP BY clause does not need to correspond to the sequence of the names in the SELECT list.

HAVING Clause

The HAVING clause defines the condition that is then applied to groups of rows. Hence, this clause has the same meaning to groups of rows that the WHERE clause has to the content of the corresponding table. The syntax of the HAVING clause is

HAVING condition

where condition contains aggregate functions or constants.

Example 6.23.1 shows the use of the HAVING clause with the aggregate function COUNT(*).

Example 6.23.1

Get project numbers for all projects employing fewer than four persons:

USE sample;
SELECT project_no
	FROM works_on
	GROUP BY project_no
	HAVING COUNT(*) < 4;Code language: PHP (php)

The result is

project_no
p3

In Example 6.6.23.1, the system uses the GROUP BY clause to group all rows according to existing values in the project_no column. After that, it counts the number of rows in each group and selects those groups with three or fewer rows. The HAVING clause can also be used without aggregate functions, as shown in Example 6.23.2.

Example 6.23.2

Group rows of the works_on table by job and eliminate those jobs that do not begin with the letter M:

USE sample;
SELECT job
	FROM works_on
	GROUP BY job
		HAVING job LIKE 'M%';Code language: PHP (php)

The result is

job
Manager

The HAVING clause can also be used without the GROUP BY clause, although doing so is uncommon in practice. In such a case, all rows of the entire table belong to a single group.

HAVING Clause

Related Lessons: