Learn SQL  /  Aggregation  /  GROUP BY

GROUP BY

GROUP BY splits rows into buckets based on shared values in one or more columns, then lets you compute an aggregate for each bucket separately.

Basic GROUP BY

R Code
SELECT class, COUNT(*) AS num_students
FROM students
GROUP BY class;
Output
class | num_students
A     | 2
B     | 2

Multiple Aggregates Per Group

R Code
SELECT class,
       COUNT(*) AS n,
       ROUND(AVG(score), 1) AS avg_score,
       MAX(score) AS top_score
FROM students
GROUP BY class;
Output
class | n | avg_score | top_score
A     | 2 | 90.0      | 92
B     | 2 | 70.5      | 76

Grouping by Multiple Columns

R Code
SELECT class, age, COUNT(*) AS n
FROM students
GROUP BY class, age;
⚠ Common Mistake
Every non-aggregated column in the SELECT list must also appear in GROUP BY. SELECT name, class, COUNT(*) GROUP BY class fails (or gives an arbitrary name) in strict databases, because name is not aggregated and not grouped - the database has no single value of name to show per group.

Advanced: Filtering Rows Before Grouping

🚀 Advanced Example
WHERE runs before GROUP BY, so it filters individual rows out of the calculation entirely - not just the final grouped result.
R Code
SELECT class, AVG(score) AS avg_score
FROM students
WHERE age < 24
GROUP BY class;
Output
class | avg_score
A     | 88.0
B     | 70.5

Key Points

  • GROUP BY collapses rows sharing the same value(s) into one summary row per group.
  • Every column in SELECT must be either aggregated (COUNT, AVG, etc.) or listed in GROUP BY.
  • Grouping by multiple columns creates one group per unique COMBINATION of those columns.
  • WHERE filters rows BEFORE grouping happens; HAVING filters groups AFTER (see the next page).