GROUP BY — Aggregating by Category

9 min read

Grouping Rows with GROUP BY

GROUP BY splits rows into groups based on one or more columns, then applies aggregate functions to each group independently. Every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate function.

sql

-- Number of students per city
SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city
ORDER BY student_count DESC;

-- Average score per course
SELECT course_id, AVG(score) AS avg_score
FROM enrolments
WHERE score IS NOT NULL
GROUP BY course_id;

-- Revenue per category
SELECT category, SUM(price) AS category_revenue
FROM courses
GROUP BY category
ORDER BY category_revenue DESC;

Think of GROUP BY as: first the WHERE filter runs to keep only matching rows, then the rows are partitioned into buckets by the group columns, then the aggregate is computed per bucket.

Ready to test yourself?

Practice SQL— quiz & coding exercises