Lesson Description
WHERE filters rows; HAVING filters groups.
Lesson 03
Lesson Description
WHERE filters rows; HAVING filters groups.
Easy Project
Mini project: Create a hiring shortlist query that filters by role, salary range, and hire date with deterministic ordering.
With GROUP BY you can produce a row per department, per customer or per doctor. Very often, though, you only care about some of those groups. The store's marketing team does not want every customer's order count; it wants the loyal customers who have ordered at least three times. The clinic manager wants the doctors who have completed five or more visits, not all fourteen.
Your first instinct might be to reach for WHERE, but WHERE cannot help here. It filters individual rows before any grouping happens, so at that point an order count per customer does not exist yet. HAVING is the clause that filters groups, after the aggregates have been computed.
SELECT grouping_column, AGGREGATE_FUNCTION(expression) AS alias
FROM table_name
WHERE row_condition -- optional: filters rows before grouping
GROUP BY grouping_column
HAVING group_condition -- filters groups after aggregation
ORDER BY ...;
The HAVING condition usually contains an aggregate, such as COUNT(*) >= 3 or AVG(salary) > 8000. You can combine conditions with AND, OR and NOT, exactly as you did in WHERE.
Which customers have placed at least three orders?
SELECT customer_id, COUNT(*) AS orders_placed
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;
This returns 10 rows, customers 4, 5, 6, 7, 8, 9, 10, 11, 13 and 19, each with exactly 3 orders. Without the HAVING line you would see 20 rows: every customer who has ordered at all. Ten of them have 2 orders and ten have 3, so the filter removed exactly half of the groups.
Which products have sold at least 5 units, and how much revenue did each bring in?
SELECT
product_id,
SUM(quantity) AS units_sold,
SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) >= 5
ORDER BY units_sold DESC, product_id;
Seven products qualify. Product 11 leads with 17 units (339.83 in revenue), but product 4 earned more money (449.55) from only 9 units, because it costs more. Note that the HAVING condition uses one aggregate (SUM(quantity)) while the SELECT list shows two. The filter and the output do not have to match.
The clinic manager wants doctors who have completed at least five appointments. That question has two filters of different kinds: "completed" is a property of each row, and "at least five" is a property of each group.
SELECT doctor_id, COUNT(*) AS completed_visits
FROM appointments
WHERE status = 'completed'
GROUP BY doctor_id
HAVING COUNT(*) >= 5
ORDER BY completed_visits DESC, doctor_id;
Five doctors make the cut: 5, 9 and 11 with 6 completed visits each, then 1 and 3 with 5. Here is what happened step by step. WHERE kept the 43 completed appointments and dropped the other 23. GROUP BY sorted those 43 rows into one group per doctor. HAVING then discarded every group with fewer than 5 rows.
If you moved the status check into HAVING, the query would not even be valid, because status is neither grouped nor aggregated. Row conditions belong in WHERE.
HR wants departments that are both well paid (average salary above 8000) and not tiny (at least 3 people), so a single high earner cannot skew the picture.
SELECT
department_id,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000
AND COUNT(*) >= 3
ORDER BY avg_salary DESC;
Three departments pass: 90 (19333.33), 80 (10833.33) and 100 (8600.00). Department 20 has an even higher average than 100, at 9500.00, but only 2 employees, so the second condition removes it.
You can also compare two aggregates with each other. Which departments have a top salary more than three times their lowest?
SELECT department_id, MIN(salary) AS lowest, MAX(salary) AS highest
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 3 * MIN(salary)
ORDER BY department_id;
Two rows: department 30 (2500.00 to 11000.00) and department 50 (2700.00 to 8200.00). These are the departments with the widest pay spread.
If you use HAVING with no GROUP BY, the whole table is treated as one group. The query returns either that one row or nothing:
SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;
This returns zero rows, because the store has only 50 orders. Change the threshold to > 10 and you get one row showing 50. It is rarely needed, but it is a useful way to make a check query return nothing when all is well.
Here is the full logical order with HAVING slotted in:
FROM: read the source rows.WHERE: filter individual rows.GROUP BY: form groups.HAVING: filter groups.SELECT: build output columns and assign aliases.ORDER BY: sort.LIMIT / OFFSET: cut the result.This order explains the rules:
| Question | Answer | Why |
|---|---|---|
Can WHERE use COUNT(*)? | No | Aggregates do not exist yet at step 2 |
Can HAVING use COUNT(*)? | Yes | Aggregates are ready at step 4 |
Can HAVING use a SELECT alias? | No, in PostgreSQL | Aliases are assigned at step 6 |
Can ORDER BY use a SELECT alias? | Yes | Step 7 comes after step 6 |
Can HAVING reference a grouped column? | Yes | Each group has one value for it |
There is also a performance angle. WHERE shrinks the data before the expensive grouping step, while HAVING works on what is left afterward. When a condition can go in either place (because it only involves grouped columns), put it in WHERE.
SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) >= 5
GROUP BY department_id;
ERROR: aggregate functions are not allowed in WHERE
Move the condition to a HAVING clause after GROUP BY.
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING headcount >= 5;
ERROR: column "headcount" does not exist
The alias is created in a later step. Repeat the expression instead: HAVING COUNT(*) >= 5. PostgreSQL will not compute it twice; the planner recognizes the same aggregate.
HAVING department_id = 80 is legal because department_id is grouped, and it returns the right answer. But it groups every department first and then throws most of them away. WHERE department_id = 80 filters first and does less work. Keep HAVING for conditions that genuinely depend on aggregates.
In example 3, HAVING COUNT(*) >= 5 counts only completed appointments, because WHERE already removed the rest. If you meant "doctors with at least five appointments of any kind, showing how many were completed," that is a different query (and the next lesson's FILTER clause makes it easy).
SELECT aliases in HAVING, so HAVING headcount >= 5 works there.The exercises below ask you to find departments of a minimum size, product categories above an average price threshold (with the average rounded for display), and hospital specialties staffed by more than a given number of doctors. Watch the comparison operators: "5 or more" and "more than 2" translate to different symbols, and getting that wrong is the most common reason a correct-looking query fails grading.
WHERE filters rows before grouping; HAVING filters groups after aggregation.HAVING but not in WHERE.WHERE and HAVING in one query, and you can use several aggregates in one HAVING condition.HAVING rather than using its alias.WHERE for clarity and speed.HAVING filters groups after the aggregates are computed, which answers questions like "departments with more than five staff" or "customers who spent over 1,000". WHERE cannot do this, because it runs before grouping and has no access to the totals.
Departments larger than five people
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY headcount DESC;Groups are built first, then HAVING keeps only those whose count is above five.
WHERE and HAVING together
SELECT o.customer_id,
SUM(oi.quantity * oi.unit_price) AS spend
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_date >= DATE '2024-01-01'
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 1000;WHERE removes old orders before grouping; HAVING removes small spenders after grouping.
WHERE COUNT(*) > 5? Why or why not?No. WHERE runs before grouping, so no counts exist yet. Aggregate conditions go in HAVING.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return department_id and employee_count for departments with 5 or more employees.
No results yet
Run a query to see results here
From the store sandbox, return category_id and avg_price (rounded to 2 decimal places) for product categories whose average price is above 50.
No results yet
Run a query to see results here
From the hospital sandbox, return each specialty and doctor_count for specialties that have more than 2 doctors, ordered by specialty.
No results yet
Run a query to see results here