Group and filter summaries

GROUP BY forms one group for each distinct grouping value, then calculates aggregates inside each group.

Suppose paid orders belong to Italy, Italy, and Denmark with totals 40, 60, and 25. This query returns one summary row per country:

SELECT country, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC;

You should get Italy with orders = 2 and revenue = 100, then Denmark with orders = 1 and revenue = 25.

WHERE filters individual rows before grouping. HAVING filters completed groups:

SELECT country, COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY country
HAVING COUNT(*) >= 10;

This means “count paid orders by country, then keep countries with at least ten.” Putting COUNT(*) >= 10 in WHERE is invalid because the groups do not exist yet. The database will raise an error if you try.

Every selected column must normally be aggregated or listed in GROUP BY:

SELECT country, customer_email, COUNT(*)
FROM orders
GROUP BY country;

That query is invalid or ambiguous. A country group can contain several customer emails. Decide whether email belongs in the grouping or should not appear.

NULL grouping values normally form one group of their own. Label that group with COALESCE when the report needs readable output:

SELECT COALESCE(country, 'Unknown') AS country, COUNT(*) AS orders
FROM orders
GROUP BY country;

That turns a blank country label into readable text in the report.

You can also filter grouped results with HAVING after the aggregates are calculated. That is the subject of the HAVING example above. The filter applies to summary rows, not individual orders. Think of HAVING as a WHERE clause that runs after the groups exist.

Try this on your own: group a practice orders table by status. Predict the row count and total for each group, then add a HAVING condition that removes one group.