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

Aggregate Questions

Five functions, and a paper almost always asks for at least one of them. The queries themselves are short; the marks are lost on what the functions do with the missing values, which is why half this page is about NULL.

1The five, on one table

1
How many employees are there in the company?
1 mark
▶Show the answer
MySQL command line client
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

“How many records/rows/employees” is always count(*). It counts rows and cannot be tripped up by a missing value anywhere in them.

2
Display the total and the average salary paid by the company.
2 marks
▶Show the answer
MySQL command line client
mysql> select sum(salary), avg(salary) from emp;
+-------------+--------------+
| sum(salary) | avg(salary) |
+-------------+--------------+
| 466000.00 | 38833.333333 |
+-------------+--------------+
1 row in set (0.00 sec)
3
Display the highest and the lowest salary.
2 marks
▶Show the answer
MySQL command line client
mysql> select max(salary), min(salary) from emp;
+-------------+-------------+
| max(salary) | min(salary) |
+-------------+-------------+
| 61000.00 | 24000.00 |
+-------------+-------------+
1 row in set (0.00 sec)

A question asking for the name of the highest-paid employee is a different and much harder thing — select name, max(salary) from emp; does not do it, and MySQL refuses that query outright. The Find the Error page shows why.

4
Display the average salary rounded to two decimal places.
1 mark
▶Show the answer
MySQL command line client
mysql> select round(avg(salary), 2) from emp;
+-----------------------+
| round(avg(salary), 2) |
+-----------------------+
| 38833.33 |
+-----------------------+
1 row in set (0.00 sec)

Functions nest: avg() runs first and round() tidies the result. Papers like this one because it needs two ideas in one line.

2The trap: aggregates skip NULL

Every aggregate except count(*) ignores NULL. It does not treat it as zero — it leaves the row out of the calculation entirely. Three employees have no bonus, and you can watch it happen:

MySQL command line client
mysql> select count(*), count(bonus), sum(bonus), avg(bonus) from emp;
+----------+--------------+------------+-------------+
| count(*) | count(bonus) | sum(bonus) | avg(bonus) |
+----------+--------------+------------+-------------+
| 12 | 9 | 25200.00 | 2800.000000 |
+----------+--------------+------------+-------------+
1 row in set (0.00 sec)
Do the division yourself
25200 ÷ 9 = 2800, which is what the server printed. 25200 ÷ 12 would be 2100. The average bonus is over the nine employees who have one, not over all twelve. If an examiner gives you a table with a blank and asks for an average, this is the whole question.
5
How many employees have been given a bonus, and how many employees are there in total?
2 marks
▶Show the answer
MySQL command line client
mysql> select count(*), count(bonus) from emp;
+----------+--------------+
| count(*) | count(bonus) |
+----------+--------------+
| 12 | 9 |
+----------+--------------+
1 row in set (0.01 sec)

The gap between the two numbers is exactly the number of NULLs in that column — twelve rows, nine bonuses, three missing.

3Counting the different values

6
How many different departments do the employees belong to?
1 mark
▶Show the answer
MySQL command line client
mysql> select count(distinct deptno) from emp;
+------------------------+
| count(distinct deptno) |
+------------------------+
| 3 |
+------------------------+
1 row in set (0.00 sec)

Three, not four — and there are two separate reasons, both worth noticing. Lata Gurung’s deptno is NULL, so she is not counted; and department 40 (Research) exists in the dept table but has nobody in it, so it cannot appear in a query that only reads emp.

4Which function does the question want?

“how many records / employees”

count(*)

“how many have a value recorded”

count(column)

“how many different / distinct”

count(distinct column)

“total / sum of”

sum(column)

“average / mean”

avg(column)

“highest / maximum / lowest”

max(column) / min(column)

Quick Check

A column has 12 rows, 3 of them NULL, and the 9 values add up to 25200. What does avg(column) return?

Quick Check

Which aggregate counts rows rather than values, and so is never affected by NULL?

← Previous
🔢 Sorting & Column Aliases
Next →
GROUP BY & HAVING 📦