Skip to content

Aggregation & GROUP BY · Easy

What is the difference between WHERE and HAVING?

Short answer

WHERE filters individual rows before they are grouped, so it cannot reference aggregates such as SUM or COUNT. HAVING filters groups after GROUP BY has run, so it can. Put row-level conditions in WHERE, which also reduces the work done, and use HAVING only for conditions on aggregated values like total revenue or order count.

Detailed answer

Example: cities where delivered orders brought in more than ₹30,000.

SELECT c.city,
       COUNT(DISTINCT o.order_id)       AS delivered_orders,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'delivered'                       -- row filter
GROUP BY c.city
HAVING SUM(oi.quantity * oi.unit_price) > 30000    -- group filter
ORDER BY revenue DESC;
  • Moving o.status = 'delivered' into HAVING would fail (status is not aggregated or grouped) or, in lenient engines, give misleading results.
  • Moving the SUM condition into WHERE is an error: aggregates do not exist at row level.

Performance angle: filtering in WHERE shrinks the data before the expensive grouping step. A HAVING clause that does not use an aggregate (e.g. HAVING city = 'Pune') works, but belongs in WHERE.