LambdaLabTM
Databases & SQL · Class 12 · Query Practice
MySQLpractice⏱️ 11 min read

GROUP BY & HAVING

The word that tells you a question needs group by is almost always each: the average salary of each department, the number of employees in each job. One answer row per group, instead of one for the whole table.

1One row per group

1
Display the number of employees in each job.
2 marks
▶Show the answer
MySQL command line client
mysql> select job, count(*) from emp group by job;
+----------+----------+
| job | count(*) |
+----------+----------+
| Manager | 3 |
| Clerk | 4 |
| Salesman | 3 |
| Analyst | 2 |
+----------+----------+
4 rows in set (0.00 sec)

Four distinct jobs, so four rows. The counts add to twelve, the whole table.

2
Display the department number, the number of employees and the average salary for each department.
3 marks
▶Show the answer
MySQL command line client
mysql> select deptno, count(*), avg(salary) from emp group by deptno;
+--------+----------+--------------+
| deptno | count(*) | avg(salary) |
+--------+----------+--------------+
| NULL | 1 | 27500.000000 |
| 10 | 4 | 36000.000000 |
| 20 | 4 | 45875.000000 |
| 30 | 3 | 37000.000000 |
+--------+----------+--------------+
4 rows in set (0.00 sec)

Look at the NULL row. Lata Gurung has no department, and group by puts all the NULLs together into a group of their own rather than dropping them. Four groups, not three.

NULL forms its own group
group by treats NULL as a value for grouping purposes, even though nothing is equal to NULL anywhere else in SQL. If a paper’s table has a blank in the grouping column, expect an extra group in the answer — and note that department 40, which has no employees at all, does not appear. A group only exists if a row put it there. Do not read anything into where the NULL group appears: a query with no order by has no guaranteed row order, and this one happens to list it first.

2HAVING — filtering the groups

where throws out rows before grouping. having throws out whole groups afterwards, and it is the only one of the two allowed to mention an aggregate.

3
Display the departments that have more than two employees.
2 marks
▶Show the answer
MySQL command line client
mysql> select deptno, count(*) from emp group by deptno having count(*) > 2;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 10 | 4 |
| 20 | 4 |
| 30 | 3 |
+--------+----------+
3 rows in set (0.00 sec)

The NULL group had only one member, so having removed it — which is a neat demonstration that having runs after the grouping, not before.

4
Display the department number and the highest salary for departments where the highest salary is above 50000.
2 marks
▶Show the answer
MySQL command line client
mysql> select deptno, max(salary) from emp group by deptno having max(salary) > 50000;
+--------+-------------+
| deptno | max(salary) |
+--------+-------------+
| 10 | 58000.00 |
| 20 | 61000.00 |
| 30 | 54000.00 |
+--------+-------------+
3 rows in set (0.00 sec)

3Using both in one query

They are not alternatives — a query can have both, and then the order of events matters. where filters the rows, then the survivors are grouped.

5
Considering only employees earning more than 25000, display the average salary of each department.
3 marks
▶Show the answer
MySQL command line client
mysql> select deptno, avg(salary) from emp where salary > 25000 group by deptno;
+--------+--------------+
| deptno | avg(salary) |
+--------+--------------+
| NULL | 27500.000000 |
| 10 | 36000.000000 |
| 20 | 45875.000000 |
| 30 | 43500.000000 |
+--------+--------------+
4 rows in set (0.00 sec)

Compare department 30 with question 2: the average rose from 37000 to 43500, because Gopal Das on 24000 was removed by the where before the averaging happened.

WHERE or HAVING?
The condition is about…UseRuns
One row on its own (salary > 25000)whereBefore grouping
A whole group (count(*) > 2)havingAfter grouping
An aggregate of any kindhavingwhere cannot see aggregates at all

4Sorting a grouped result

6
Display the total salary paid for each job, largest total first.
2 marks
▶Show the answer
MySQL command line client
mysql> select job, sum(salary) from emp group by job order by sum(salary) desc;
+----------+-------------+
| job | sum(salary) |
+----------+-------------+
| Manager | 173000.00 |
| Clerk | 103000.00 |
| Analyst | 96500.00 |
| Salesman | 93500.00 |
+----------+-------------+
4 rows in set (0.00 sec)

order by comes last, after group by, and it may sort by an aggregate. Note that four clerks out-earn two analysts in total while earning far less each — a nice reminder that sum and avg answer different questions.

Quick Check

A table has 12 rows; one row has NULL in the grouping column. How many groups does GROUP BY produce if the other 11 rows use 3 values?

Quick Check

Which clause can contain count(*) > 2?

Quick Check

In 'where salary > 25000 group by deptno', when is the where applied?

← Previous
🧮 Aggregate Questions
Next →
Math, Text & Date Functions 🔣