After this lesson you can answer questions such as "which departments have at least four people?" or "which projects consumed more than 1,000 hours?". These ask about the result of a summary, and the clause that handles them is HAVING.
All examples run on the sample company database, tested on SQLite.
The problem HAVING solves
Suppose you want to see only departments with at least four employees. The natural first attempt is to put the condition in WHERE:
SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) >= 4
GROUP BY department_id;This fails. SQLite says "misuse of aggregate: COUNT()" and the other databases report a similar error. The count of a group does not exist when WHERE runs, because WHERE looks at one row at a time, before any rows are grouped. You need a clause that runs after grouping. That clause is HAVING.
SELECT department_id,
COUNT(*) AS people
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 4;+---------------+--------+
| department_id | people |
+---------------+--------+
| 1 | 6 |
| 2 | 5 |
+---------------+--------+The database forms the groups, calculates COUNT(*) for each, and then discards the groups that fail the test. Only departments 1 and 2 remain.
WHERE versus HAVING
Both clauses filter, but they filter different things at different moments:
WHEREworks on rows, beforeGROUP BY. It cannot see aggregates.HAVINGworks on groups, afterGROUP BY. It can use aggregates and the grouping columns.
A good rule is to put every condition that can go into WHERE there, and keep HAVING for conditions that need an aggregate. Filtering early means fewer rows to group, which is faster and easier to read.
Here the two are used together. First WHERE keeps only people hired in 2020 or later. Then the survivors are grouped, and HAVING keeps departments that still have at least two of them:
SELECT department_id,
COUNT(*) AS recent_hires,
ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;+---------------+--------------+------------+
| department_id | recent_hires | avg_salary |
+---------------+--------------+------------+
| 1 | 4 | 86250.0 |
| 3 | 2 | 66000.0 |
| 2 | 3 | 64000.0 |
| 5 | 2 | 56000.0 |
+---------------+--------------+------------+To see what each clause did, here is the same query without HAVING:
SELECT department_id,
COUNT(*) AS recent_hires
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id
ORDER BY department_id;+---------------+--------------+
| department_id | recent_hires |
+---------------+--------------+
| NULL | 1 |
| 1 | 4 |
| 2 | 3 |
| 3 | 2 |
| 4 | 1 |
| 5 | 2 |
+---------------+--------------+The unassigned person (department NULL) and the Finance department each had only one recent hire, so HAVING COUNT(*) >= 2 removed both.
HAVING with different aggregates
You can test any aggregate, not just COUNT. Departments whose average salary is above 80000, ordered by that average:
SELECT department_id,
COUNT(*) AS people,
ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000
ORDER BY avg_salary DESC;+---------------+--------+------------+
| department_id | people | avg_salary |
+---------------+--------+------------+
| 1 | 6 | 108333.0 |
| NULL | 1 | 95000.0 |
| 4 | 2 | 92500.0 |
| 2 | 5 | 86400.0 |
| 3 | 3 | 80667.0 |
+---------------+--------+------------+The unassigned person forms a group with department_id NULL and an average of 95000, so that group appears here too. Projects that consumed more than 1,000 hours in total:
SELECT project_id,
SUM(hours) AS total_hours
FROM assignments
GROUP BY project_id
HAVING SUM(hours) > 1000
ORDER BY total_hours DESC;+------------+-------------+
| project_id | total_hours |
+------------+-------------+
| 1 | 1160 |
| 2 | 1100 |
| 4 | 1050 |
+------------+-------------+Job titles shared by more than one person, which is a handy duplicate finder:
SELECT job_title,
COUNT(*) AS people
FROM employees
GROUP BY job_title
HAVING COUNT(*) > 1
ORDER BY people DESC, job_title;+-------------------+--------+
| job_title | people |
+-------------------+--------+
| Account Executive | 2 |
| Developer | 2 |
| Sales Associate | 2 |
| Senior Developer | 2 |
| Support Engineer | 2 |
+-------------------+--------+You can combine conditions with AND and OR in HAVING, exactly as in WHERE: HAVING COUNT(*) >= 3 AND AVG(salary) < 90000.
Conditions on the grouping column
HAVING may also test the grouping column itself, because the column is available for every group:
SELECT department_id,
COUNT(*) AS people
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL AND COUNT(*) < 4;+---------------+--------+
| department_id | people |
+---------------+--------+
| 3 | 3 |
| 4 | 2 |
| 5 | 3 |
+---------------+--------+It works, but a condition on the grouping column is better written in WHERE, where it removes rows before they are grouped:
SELECT department_id,
COUNT(*) AS people
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING COUNT(*) < 4;+---------------+--------+
| department_id | people |
+---------------+--------+
| 3 | 3 |
| 4 | 2 |
| 5 | 3 |
+---------------+--------+Both return departments 3, 4 and 5 with their counts, but the second is the cleaner habit.
Aliases and HAVING
The select list gives COUNT(*) the name people. Can HAVING use that name? It depends on the database. SQLite and MySQL accept HAVING people >= 4. PostgreSQL and SQL Server do not, and report an unknown column. The portable choice is to repeat the aggregate expression, as the examples above do. It looks repetitive, but it works everywhere, and the database computes the value only once.
HAVING without GROUP BY
If a query has aggregates but no GROUP BY, the whole table forms one group, and HAVING decides whether that single row appears at all:
SELECT COUNT(*) AS people
FROM employees
HAVING COUNT(*) > 10;+--------+
| people |
+--------+
| 20 |
+--------+This is rare in practice, but it shows that HAVING is about groups, even when there is just one.
Differences between databases
- Alias in HAVING. SQLite and MySQL accept it. PostgreSQL and SQL Server do not. Repeat the aggregate instead.
- MySQL specifics. For more, see HAVING and WITH ROLLUP in MySQL.
Common mistakes
- Putting an aggregate in
WHERE. It fails. UseHAVING. - Putting an ordinary row condition in
HAVING. It works, but filters late and reads badly. UseWHERE. - Using the alias in HAVING and finding it fails after moving to PostgreSQL or SQL Server.
- Forgetting that
WHEREchanges the aggregates. Counts and averages in a grouped query only cover the rows that survivedWHERE. - Expecting
HAVINGto sort or limit. It only filters. UseORDER BYandLIMITfor those.
Exercise
Find the job titles that have at least two people and an average salary above 60000. Show the title, the number of people and the average salary rounded to a whole number, with the best-paid titles first. Then change the query so that only employees hired before 2021 are counted.
Show one possible answer
SELECT job_title,
COUNT(*) AS people,
ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY job_title
HAVING COUNT(*) >= 2 AND AVG(salary) > 60000
ORDER BY avg_salary DESC;+-------------------+--------+------------+
| job_title | people | avg_salary |
+-------------------+--------+------------+
| Senior Developer | 2 | 125000.0 |
| Account Executive | 2 | 90000.0 |
| Developer | 2 | 80000.0 |
+-------------------+--------+------------+SELECT job_title,
COUNT(*) AS people,
ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE hire_date < '2021-01-01'
GROUP BY job_title
HAVING COUNT(*) >= 2 AND AVG(salary) > 60000
ORDER BY avg_salary DESC;+-------------------+--------+------------+
| job_title | people | avg_salary |
+-------------------+--------+------------+
| Senior Developer | 2 | 125000.0 |
| Account Executive | 2 | 90000.0 |
+-------------------+--------+------------+Key points
- HAVING filters groups after GROUP BY and aggregation. WHERE filters individual rows before grouping.
- HAVING is where you put conditions on aggregates, such as HAVING COUNT(*) >= 3 or HAVING AVG(salary) > 80000.
- Put conditions on ordinary columns in WHERE, not HAVING. It is clearer, and the database can discard rows earlier.
- Repeat the aggregate in HAVING. Using the select-list alias there works in SQLite and MySQL but not in PostgreSQL or SQL Server.