Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 19 of 49

HAVING: filtering groups after GROUP BY

Use HAVING to keep only the groups that pass a test on an aggregate, and learn exactly how it differs from WHERE, with checked output on the sample database.

13 minSummarising dataUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonFilter groups with HAVING, choose correctly between WHERE and HAVING, and combine both in one query.

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:

sql
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.

sql
SELECT department_id,
       COUNT(*) AS people
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 4;
result
+---------------+--------+
| 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:

  • WHERE works on rows, before GROUP BY. It cannot see aggregates.
  • HAVING works on groups, after GROUP 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:

sql
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;
result
+---------------+--------------+------------+
| 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:

sql
SELECT department_id,
       COUNT(*) AS recent_hires
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id
ORDER BY department_id;
result
+---------------+--------------+
| 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:

sql
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;
result
+---------------+--------+------------+
| 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:

sql
SELECT project_id,
       SUM(hours) AS total_hours
FROM assignments
GROUP BY project_id
HAVING SUM(hours) > 1000
ORDER BY total_hours DESC;
result
+------------+-------------+
| project_id | total_hours |
+------------+-------------+
|          1 |        1160 |
|          2 |        1100 |
|          4 |        1050 |
+------------+-------------+

Job titles shared by more than one person, which is a handy duplicate finder:

sql
SELECT job_title,
       COUNT(*) AS people
FROM employees
GROUP BY job_title
HAVING COUNT(*) > 1
ORDER BY people DESC, job_title;
result
+-------------------+--------+
|     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:

sql
SELECT department_id,
       COUNT(*) AS people
FROM employees
GROUP BY department_id
HAVING department_id IS NOT NULL AND COUNT(*) < 4;
result
+---------------+--------+
| 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:

sql
SELECT department_id,
       COUNT(*) AS people
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING COUNT(*) < 4;
result
+---------------+--------+
| 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:

sql
SELECT COUNT(*) AS people
FROM employees
HAVING COUNT(*) > 10;
result
+--------+
| 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. Use HAVING.
  • Putting an ordinary row condition in HAVING. It works, but filters late and reads badly. Use WHERE.
  • Using the alias in HAVING and finding it fails after moving to PostgreSQL or SQL Server.
  • Forgetting that WHERE changes the aggregates. Counts and averages in a grouped query only cover the rows that survived WHERE.
  • Expecting HAVING to sort or limit. It only filters. Use ORDER BY and LIMIT for 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
sql
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;
result
+-------------------+--------+------------+
|     job_title     | people | avg_salary |
+-------------------+--------+------------+
| Senior Developer  |      2 |   125000.0 |
| Account Executive |      2 |    90000.0 |
| Developer         |      2 |    80000.0 |
+-------------------+--------+------------+
sql
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;
result
+-------------------+--------+------------+
|     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.

Check yourself

1. Which clause can use COUNT(*) > 3 as a condition?
2. You want only employees hired after 2020 to be counted, per department. Where does the hire_date condition belong?
3. What does this query return? SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) >= 5
4. Which version works in PostgreSQL and SQL Server?