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 three thousand five hundred. Most people reach for where. But where can't check a total. Having can. Let's see the difference.
- Sales per region. Here's sales per region. Orders joined to customers on the customer ID, grouped by region. Four regions, four totals. Now let's keep only the ones above 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, the way people usually try it. Where the sum of the order total is more than three thousand five hundred, before the group by. An error. Where looks at one row at a time, before the groups are made. At that point there's no total to check.
WHERE SUM(o.order_total) > 3500 GROUP BY c.region;
HAVING
- Use HAVING. So move the condition. Group by first, then having, with the same sum. Having checks each group once its total exists. Three regions beat the target. West didn't make it.
GROUP BY c.region HAVING SUM(o.order_total) > 3500; - Above target. Every total left is over three thousand five hundred.
Raise the bar
- A new target. Targets change. Make it four thousand. Run it again. Only one region is left.
4000 - Only North. North, on its own. One word changed, and the report answers a new question.
Recap
- WHERE vs HAVING. So, where filters rows before they're grouped. Having filters the groups after group by, so it can check sums and counts. Use both in one query when you need to.
