Free practical guide

SQL GROUP BY and HAVING, explained with an example

GROUP BY answers questions about groups rather than individual rows. It is how you turn a list of transactions into totals by region, product or month. HAVING lets you filter those totals.

A four-order example

A fictional company has four orders. You want to know which regions have at least €150 of positive order value.

WITH orders(region, amount) AS (
  VALUES ('North', 120), ('North', 80),
         ('South', 90), ('South', 10)
)
SELECT region,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
WHERE amount > 0
GROUP BY region
HAVING SUM(amount) >= 150
ORDER BY total_amount DESC;

WHERE versus HAVING

WHERE acts on individual rows before grouping. In this example it excludes non-positive orders. GROUP BY then creates one row per region. HAVING filters those groups after SUM is calculated.

If you wrote WHERE SUM(amount) >= 150 instead of HAVING, the query would be invalid because the WHERE clause cannot filter an aggregate result in the same SELECT.

Three checks worth making

1. Count the right thing. COUNT(*) counts rows, while COUNT(column) ignores NULL values in that column.
2. Check the grain. If an order has several item rows, COUNT(*) might count items instead of orders. Decide whether COUNT(DISTINCT order_id) is needed.
3. Explain missing groups. If a region disappears, check both the row-level WHERE condition and the group-level HAVING condition.

Continue learning

Practise counting and summing in SQL for Analysis, Module 4. If you want to run an independent query, open Insyt's free SQL practice page. For the formal rules, see SQLite aggregate functions.