GROUP BY and HAVING

Splitting Rows Into Groups

GROUP BY divides rows into groups, then an aggregate runs once per group - one output row per group.

SELECT city, COUNT(*) AS members
FROM customers
GROUP BY city;

SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

The Golden Rule

Every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause.

-- WRONG: country not allowed unless it is grouped
SELECT city, country, COUNT(*) FROM customers GROUP BY city;

-- RIGHT
SELECT city, country, COUNT(*) FROM customers GROUP BY city, country;

Filtering Groups With HAVING

HAVING filters the groups after aggregation, the way WHERE filters rows before it.

SELECT customer_id, SUM(total) AS lifetime
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;

SELECT city, COUNT(*) AS c
FROM customers
GROUP BY city
HAVING COUNT(*) >= 5;

WHERE vs HAVING

• WHERE runs before grouping; it cannot use aggregates.
• HAVING runs after grouping; it can use aggregates.
• Filter early with WHERE for speed, and use HAVING only for group conditions.

Key Points

• GROUP BY creates one output row per group.
• All non-aggregated SELECT columns must be grouped.
• HAVING filters groups; WHERE filters rows.
• This is how every sales, traffic and user report starts.