GROUP BY
Run aggregates per group instead of the whole table.
Questions this answers
- →Which genre has the most streams?
- →How many orders did each customer place?
- →What's the average salary per department?
- →Which product categories generated the most revenue?
- →How many users signed up each month?
How it works
Without GROUP BY, aggregate functions collapse the entire table into a single row. GROUP BY splits the table into groups first — one per unique value in the grouping column — then runs the aggregate on each group. Think of it as sorting your data into piles, then counting each pile.
Basic grouping
SELECT genre, COUNT(*) AS total_streams FROM plays GROUP BY genre ORDER BY total_streams DESC;
Filter groups with HAVING
WHERE filters rows before grouping. HAVING filters groups after. Use HAVING when your condition involves an aggregate.
SELECT genre, COUNT(*) AS total_streams FROM plays GROUP BY genre HAVING COUNT(*) > 500;
💡 WHERE total_streams > 500 fails — WHERE runs before COUNT, so the alias doesn't exist yet.
Multiple grouping columns
SELECT year, genre, COUNT(*) AS streams FROM plays GROUP BY year, genre ORDER BY year, streams DESC;