SQL HAVING vs WHERE: Filter Groups
WHERE filters rows before they are grouped, so it cannot check a total. HAVING filters the groups after GROUP BY. Find the regions whose sales beat a target, and see the error WHERE gives you.
Step by step
Filter totals
- Beat the target. Sales wants the regions that beat a target of 3500. Most people try WHERE first, but WHERE can't check a total. HAVING can.
- Sales per region. Start with sales per region. The query joins orders to customers on customer_id, adds up order_total with SUM, and groups the result by c.region. Running it returns 4 rows: one total for each region. Next, keep only the regions above the target.
SELECT c.region, SUM(o.order_total) AS sales FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id GROUP BY c.region;
WHERE fails
- Try WHERE. First, try it the usual way. Add WHERE SUM(o.order_total) > 3500 just before GROUP BY c.region. The query fails with an error. WHERE checks one row at a time, before the groups are made, so there is no total for it to check yet.
WHERE SUM(o.order_total) > 3500 GROUP BY c.region;
HAVING
- Use HAVING. Move the condition. Put GROUP BY c.region first, then HAVING SUM(o.order_total) > 3500. HAVING checks each group after its total has been worked out. This time it runs and returns 3 rows. 3 regions beat the target, and West drops out.
GROUP BY c.region HAVING SUM(o.order_total) > 3500; - Above target. Look down the sales column: every total left is over 3500.
Raise the bar
- A new target. Targets change. In the HAVING line, change 3500 to 4000. Run the query again. Only 1 row is left.
4000 - Only North. North is the only region over 4000. You changed one number and the same query answers a new question.
Recap
- WHERE vs HAVING. WHERE filters rows before they are grouped. HAVING filters groups after GROUP BY, so it can check sums and counts. You can use both in one query: WHERE removes rows first, then HAVING removes groups.
Common mistakes
- Putting SUM or COUNT in WHERE. WHERE runs before the groups exist, so the database returns an error. Move the condition into a HAVING clause after GROUP BY.
- Using HAVING for a row filter. A condition on single rows, like an order date or a status, belongs in WHERE. That way those rows are removed before the totals are added up, and HAVING only checks the totals.
- Using the column alias in HAVING. HAVING sales > 3500 works in MySQL, SQLite and Oracle 23ai, but not in SQL Server, PostgreSQL or older Oracle versions. Repeat the full SUM(o.order_total) so the query works in every database.
- Writing HAVING before GROUP BY. The order is fixed: WHERE, then GROUP BY, then HAVING, then ORDER BY. Put them in the wrong order and you get a syntax error.
When a business analyst uses this
Before a go-live readiness review, a business analyst can find which components still have too many open defects: SELECT component, COUNT(*) FROM defects WHERE status = 'Open' GROUP BY component HAVING COUNT(*) > 5. WHERE keeps only the open defects, and HAVING keeps only the components over the limit.
Questions people ask
What is the difference between WHERE and HAVING in SQL?
WHERE filters single rows before they are grouped, so it can't use SUM, COUNT or other totals. HAVING filters groups after GROUP BY, so it can check those totals.
Can you use WHERE and HAVING in the same query?
Yes. WHERE removes rows first, then GROUP BY makes the groups, then HAVING removes groups whose totals don't meet the condition.
Does HAVING vs WHERE work the same way in SQL Server?
Yes. SQL Server doesn't allow a total like SUM in WHERE and gives an error, so you filter totals with HAVING after GROUP BY. It also doesn't let HAVING use a column alias, so repeat the full SUM.
