TrickySQL#726 min readJul 11, 2026
HAVING
practice
Why interviewers love this: HAVING looks tiny, but it reveals whether you understand when SQL filters rows versus groups.
Question: What is HAVING in SQL, and how is it different from WHERE?
Answer: HAVING filters groups after aggregation, while WHERE filters individual rows before grouping happens. That means HAVING is the clause you use when your condition depends on an aggregate like COUNT, SUM, or AVG. A simple way to remember it: first SQL collects rows into buckets, then HAVING decides which buckets are worth keeping.
Interview-Ready Answer: I use HAVING when I need to filter grouped results, especially with aggregates. WHERE works on rows before grouping, so it cannot directly use aggregate results like COUNT(*) or AVG(salary). For example, if I want departments with more than 10 employees, I would GROUP BY department and then apply HAVING COUNT(*) > 10. One useful detail is that HAVING can also be used without GROUP BY, where it filters the single implicit group.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EHAVING%20clause%3C%2Ftext%3E%3C%2Fsvg%3E)
What HAVING does
HAVING is the filter for grouped data. Think of SQL as doing work in stages: it reads rows, optionally filters them with WHERE, groups them with GROUP BY, computes aggregates, and then applies HAVING to the finished groups.
- Read the rows. SQL starts with the table data.
- Apply
WHEREfirst. This removes rows before any grouping. It is ideal for simple row rules likestatus = 'paid'. - Build groups.
GROUP BYcollects rows that share the same key, like all sales for one department. - Compute aggregates. SQL calculates values such as
COUNT(*),SUM(amount), orAVG(amount)for each group. - Apply
HAVING. This keeps only the groups that satisfy the aggregate condition.
WHERE vs HAVING
| Clause | Works on | Can use aggregates? | Typical use |
|---|---|---|---|
| WHERE | Rows | No | Filter raw data |
| HAVING | Groups | Yes | Filter summaries |
Memory model: WHERE is the bouncer at the door; HAVING is the judge after the crowd has been counted.
When to use it
- Find departments with
COUNT(*) > 5. - Find customers whose total spend
SUM(amount)exceeds a threshold. - Find products with an average rating above 4.5.
- Find groups that meet multiple aggregate rules, like
COUNT(*) > 3andAVG(score) > 80.
How it compares to alternatives
| Approach | Best for | Limitation |
|---|---|---|
WHERE | Row-level filtering | No aggregates |
HAVING | Post-aggregation filtering | Runs after grouping |
| Subquery | Reusing aggregates in outer query | Often more verbose |
Performance notes: SQL still has to inspect the relevant rows to form groups. If a query scans 1,000,000 rows, aggregation is usually at least linear in the number of input rows; a hash aggregate is often close to O(n) on average, while a sort-based aggregate can behave like O(n log n). Good indexes help most when a condition can move to WHERE before grouping. Optimizers in modern databases sometimes push a safe HAVING predicate down into WHERE, but only when the logic is equivalent.
Important edge cases
HAVINGwithoutGROUP BY: the whole result set becomes one implicit group. This is useful for checks like “is total revenue above target?”NULLvalues: aggregates handle them differently.COUNT(*)counts rows, butCOUNT(column)ignoresNULLs.- Non-aggregate columns: if you reference a selected column that is not grouped, many databases reject it because it is ambiguous.
- Pushdown traps: moving a condition from
HAVINGtoWHEREcan change results if the condition depends on the aggregate itself.
Real-world story
Imagine a checkout analytics service for an e-commerce company. Every night it builds a report of stores whose daily revenue exceeded a target and had at least 20 orders. The engineer writes a query with GROUP BY store_id and HAVING SUM(amount) > 5000 AND COUNT(*) > 20, then feeds the results into an executive dashboard.
What goes wrong when someone misunderstands HAVING? A common bug is moving the revenue condition into WHERE amount > 5000. That changes the meaning completely: instead of checking total revenue per store, it only keeps individual orders above 5000, so almost every store disappears. The dashboard suddenly shows zero stores meeting target, the finance team panics, and logs show rows being processed normally but the final report is empty. In production, the symptom is not a crash; it is a quiet, believable wrong answer — the hardest kind of bug to catch.
SQL
-- Demonstration: HAVING filters grouped results, while WHERE filters raw rows.
-- This script is intentionally small, but it shows both the normal case and an edge case.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Ava'),
(2, 'Ben'),
(3, 'Chloe'),
(4, 'Drew');
INSERT INTO orders (order_id, customer_id, amount, status) VALUES
(101, 1, 40.00, 'paid'),
(102, 1, 70.00, 'paid'),
(103, 1, 15.00, 'refunded'),
(104, 2, 25.00, 'paid'),
(105, 2, 35.00, 'paid'),
(106, 3, 200.00, 'refunded');
-- Good use of HAVING: keep only customers whose paid-order total is above 100.
-- WHERE cannot replace this, because the filter depends on SUM(...) after grouping.
SELECT
o.customer_id,
SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) AS paid_total,
COUNT(*) AS total_rows
FROM orders AS o
GROUP BY o.customer_id
HAVING SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) > 100
ORDER BY o.customer_id;
-- Edge case: find customers with NO paid orders.
-- LEFT JOIN preserves customers even when they have no matching paid orders.
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS paid_order_count
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) = 0
ORDER BY c.customer_id;
-- Common mistake example (kept as a comment so the script still runs):
-- This is invalid because COUNT(*) is an aggregate and cannot be used in WHERE.
-- SELECT customer_id FROM orders WHERE COUNT(*) > 1 GROUP BY customer_id;
-- HAVING without GROUP BY: the whole table becomes one implicit group.
SELECT
SUM(amount) AS total_order_amount
FROM orders
HAVING SUM(amount) >= 300;Follow-up & Tricky Questions:
- How do you rewrite a
HAVINGquery if performance is slow? First check whether part of the condition can move toWHEREbefore grouping. That reduces the number of rows entering the aggregate and is often the biggest win. - Can
HAVINGbe used withoutGROUP BY? Yes. In that case, SQL treats the full result as one group, soHAVINGfilters the single aggregate result. - What is the difference between
COUNT(*)andCOUNT(column)inHAVING?COUNT(*)counts rows, including rows where columns areNULL;COUNT(column)ignoresNULLvalues, which can change the groups that pass the filter. - Can
HAVINGreference columns that are not inGROUP BY? Usually no, unless they are wrapped in an aggregate or your database has a special extension. Standard SQL requires grouped columns or aggregates for unambiguous results. - Does the database always evaluate
HAVINGafter aggregation? Logically yes, but the optimizer may push a safe predicate earlier. The final result must stay the same, even if the engine changes the physical execution plan. - Tricky: If I write
HAVING status = 'paid'after grouping bycustomer_id, is that valid? Usually not, becausestatusis not grouped or aggregated, so the database cannot choose a single value for the group. You needWHERE status = 'paid'or an aggregate-based condition such asSUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) > 0. - Tricky: Is
HAVING COUNT(*) > 0useful? In a normal grouped query it is usually redundant, because groups only exist when rows are present. It can still appear in outer joins or subqueries, where it helps preserve intent. - Tricky: Why does moving a predicate from
HAVINGtoWHEREsometimes change the answer? BecauseWHEREremoves rows before the aggregate is computed, so the sum or count itself changes. If the condition depends on the final aggregate, it belongs inHAVING.
Common Mistakes:
- Using
WHEREfor aggregate logic — correction: if the condition depends onCOUNT,SUM, orAVG, useHAVING. - Forgetting the order of operations — correction: think
WHEREfirst, thenGROUP BY, thenHAVING. - Filtering the wrong level — correction:
WHEREchanges the raw input set;HAVINGchanges the final group list. - Ignoring
NULLbehavior — correction: know thatCOUNT(column)skipsNULLs, which can surprise you in grouped filters.
Memory Hook: “WHERE opens the boxes; HAVING sorts the boxes.” First you pick the rows, then you decide which grouped totals are good enough to keep.
Cheat Sheet:
WHERE= row filter before grouping.HAVING= group filter after aggregation.- Use
HAVINGforCOUNT,SUM,AVG,MIN,MAX. HAVINGcan work withoutGROUP BYon the single implicit group.- If a condition can safely move to
WHERE, the query often gets faster.
Practice Tasks:
- Write a query that returns departments with more than 3 employees.
- Write a query that returns customers whose total order value is at least 500.
- Rewrite one of those queries so that part of the filter moves from
HAVINGtoWHERE, then compare the results.