GROUP BY vs HAVING in SQL: What's the Difference?

This one trips up almost everyone learning SQL, and it comes down to a single idea: WHERE filters rows before grouping. HAVING filters groups after grouping. Once that clicks, the rest is just syntax.

What GROUP BY actually does

GROUP BY collapses many rows into one row per unique value (or combination of values) in the columns you name. It's almost always paired with an aggregate function — COUNT, SUM, AVG, MIN, MAX — that summarizes each group.

SELECT dept_id,
       COUNT(*)    AS headcount,
       MIN(salary) AS lowest_salary,
       MAX(salary) AS highest_salary
FROM Employees
GROUP BY dept_id
ORDER BY dept_id;

Instead of one row per employee, this returns one row per department, with the headcount and the salary range computed across all employees in that group. Every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function — the database wouldn't know which employee's raw salary to show for a group otherwise.

PostgreSQL, SQL Server and MySQL in its default mode enforce that rule with an error. SQLite, which runs this site's playground, does not: it accepts the query below and fills first_name from whichever row of each group it happened to use. Run it and you get one name per department that means nothing, which is why the playground adds a warning under the result instead of staying silent.

-- Runs in SQLite; PostgreSQL, SQL Server and MySQL (default mode) reject it
SELECT dept_id, first_name, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id
ORDER BY dept_id;

Why WHERE can't filter on COUNT() or SUM()

SQL evaluates clauses in roughly this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE runs before grouping happens, so at that point there's no such thing as "the department's headcount" yet — each row is still an individual employee. This is why the following is invalid:

-- Invalid: COUNT(*) doesn't exist yet at the WHERE stage
SELECT dept_id, COUNT(*) AS headcount
FROM Employees
WHERE COUNT(*) > 2
GROUP BY dept_id;

HAVING: WHERE, but for groups

HAVING runs after GROUP BY, once the aggregates have been calculated — so it can filter on them directly:

SELECT dept_id, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 2;

This returns only departments with more than two employees — the small departments are dropped from the output entirely.

Notice that the condition repeats COUNT(*) instead of using the headcount alias. Whether HAVING can see an alias from the SELECT list depends on the database. MySQL and SQLite accept it; PostgreSQL and SQL Server reject it, because in the logical order above HAVING runs before SELECT has named anything. The playground runs SQLite, so this version works here and returns the same two departments:

-- Works in SQLite and MySQL; PostgreSQL and SQL Server reject it
SELECT dept_id, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id
HAVING headcount > 2;

Repeating the aggregate is the form that runs everywhere, so it is the habit worth keeping.

Using both together

WHERE and HAVING aren't mutually exclusive — a real query often uses both, each doing its own job:

SELECT c.company, SUM(o.order_total) AS revenue
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'      -- drop cancelled orders BEFORE summing
GROUP BY c.company
HAVING SUM(o.order_total) > 5000    -- keep only high-revenue customers
ORDER BY revenue DESC;

Here, WHERE excludes cancelled orders row-by-row before anything is totaled, then GROUP BY totals revenue per company, and finally HAVING keeps only the companies whose total exceeds 5,000.

Grouping by more than one column

List several columns in GROUP BY and you get one row per unique combination of them, not one row per column. This is how you break a total down along two dimensions at once — orders per department per status, say:

SELECT e.dept_id, o.status, COUNT(*) AS orders
FROM Orders o
JOIN Employees e ON e.emp_id = o.emp_id
GROUP BY e.dept_id, o.status
ORDER BY e.dept_id, o.status;

The order you list the columns in does not change which groups come back, only how you might want to sort the output afterwards. Combinations that have no rows at all simply do not appear — GROUP BY can only summarise data that exists.

The aggregate functions you will actually use

Five cover almost everything: COUNT for how many, SUM for a total, AVG for a mean, and MIN and MAX for the extremes. They can all appear in the same SELECT:

SELECT category,
       COUNT(*)        AS items,
       AVG(unit_price) AS avg_price,
       MIN(unit_price) AS cheapest,
       MAX(unit_price) AS priciest
FROM Products
GROUP BY category
ORDER BY category;

COUNT(*) vs COUNT(column) — not the same thing

This distinction matters more often than people expect. COUNT(*) counts rows. COUNT(some_column) counts rows where that column is not NULL. When the column has no missing values the two agree, which is exactly why the difference goes unnoticed until it causes a bug.

SELECT dept_id,
       COUNT(*)          AS employees,
       COUNT(manager_id) AS have_a_manager
FROM Employees
GROUP BY dept_id
ORDER BY dept_id;

Department 10 has five employees but only three with a manager, and department 20 has four people but two with a manager. Across the four departments, five employees have no manager_id — they are the top of the reporting tree — and COUNT(manager_id) skips every one of them. Reading have_a_manager as a headcount would be wrong, and nothing in the output warns you. The aggregate functions guide shows the same effect on AVG, where it quietly changes the answer.

Quick reference

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Headcount per department Medium
• Biggest departments first Medium
• Only the multi-person departments Medium
• Big spenders Hard

See all 42 exercises →

Frequently asked questions

Related reading

• SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX
• Find and Delete Duplicate Rows in SQL
• The SQL WHERE Clause: Filtering Data with Every Operator
• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL
• SQL CASE WHEN Explained: Conditional Logic in Queries
• SQL Date Functions: YEAR, MONTH, DATEDIFF & Ranges