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. 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;

The two Italian rows contribute to one Italian count and sum. Denmark produces its own summary.

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.

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.

Your action is to 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.