GROUP BY

Introduction

So far, aggregate functions like COUNT(), SUM(), and AVG() have worked on an entire table at once — for example, counting every row, or averaging every mark. But very often you want a summary per category instead: how many students are in each grade, or the average marks for each grade, rather than one single number for the whole table.

This is exactly what GROUP BY is for. It collapses rows that share the same value in a column into a single group, so an aggregate function can then be calculated separately for each group.

What is GROUP BY?

GROUP BY takes all the rows in a result set and arranges them into groups based on the values in one or more columns. All rows with the same value end up in the same group.

Consider this students table, which will be used throughout this lesson.

Grouping this table by the grade column creates three groups: all the "A" students together, all the "B" students together, and all the "C" students together. On its own, GROUP BY does not display anything useful — it becomes powerful once you pair it with an aggregate function.

SELECT grade
FROM students
GROUP BY grade;

+-------+
| grade |
+-------+
| A     |
| B     |
| C     |
+-------+

Notice that even though there are six rows in the table, only three rows come back — one for each distinct grade. This is the core behavior of GROUP BY: it collapses the table down to one row per group.

GROUP BY with COUNT()

The most common use of GROUP BY is counting how many rows fall into each group. Adding COUNT(*) tells MySQL to count the rows within each group, rather than the whole table.

SELECT grade, COUNT(*) AS total_students
FROM students
GROUP BY grade;

+-------+----------------+
| grade | total_students |
+-------+----------------+
| A     | 3              |
| B     | 2              |
| C     | 1              |
+-------+----------------+

GROUP BY with Other Aggregates

GROUP BY works with every aggregate function, not just COUNT(). You can calculate an average, total, minimum, or maximum per group in exactly the same way.

SELECT grade, AVG(marks) AS average_marks
FROM students
GROUP BY grade;

+-------+---------------+
| grade | average_marks |
+-------+---------------+
| A     | 91.6667       |
| B     | 71.5000       |
| C     | 55.0000       |
+-------+---------------+

You can also select multiple aggregates for the same groups in a single query.

SELECT grade, COUNT(*) AS total_students, AVG(marks) AS average_marks, MAX(marks) AS top_marks
FROM students
GROUP BY grade;

+-------+----------------+---------------+-----------+
| grade | total_students | average_marks | top_marks |
+-------+----------------+---------------+-----------+
| A     | 3              | 91.6667       | 95        |
| B     | 2              | 71.5000       | 74        |
| C     | 1              | 55.0000       | 55        |
+-------+----------------+---------------+-----------+

Worked Example: Students per Grade

Suppose you want a quick report showing exactly how many students are in each grade, ordered from the most common grade to the least common. You can combine GROUP BY with ORDER BY to sort the grouped results.

SELECT grade, COUNT(*) AS total_students
FROM students
GROUP BY grade
ORDER BY total_students DESC;

+-------+----------------+
| grade | total_students |
+-------+----------------+
| A     | 3              |
| B     | 2              |
| C     | 1              |
+-------+----------------+

MySQL processes this query in a logical order: it first finds the matching rows, then groups them by grade, then counts each group, and finally sorts the grouped rows by total_students. ORDER BY always runs after GROUP BY, so it sorts the already-grouped results.

The GROUP BY Rule

There is one strict rule to remember: every column in the SELECT list must either be inside an aggregate function, or listed in the GROUP BY clause. MySQL needs this because once rows are collapsed into groups, it no longer has a single value for a non-grouped, non-aggregated column.

-- This is INVALID: "name" is not aggregated and not in GROUP BY
SELECT grade, name, COUNT(*)
FROM students
GROUP BY grade;

-- Valid: name is added to GROUP BY, so grouping happens per student per grade
SELECT grade, name, marks
FROM students
GROUP BY grade, name, marks;

+-------+-------+-------+
| grade | name  | marks |
+-------+-------+-------+
| A     | Alex  | 92    |
| A     | Leo   | 95    |
| A     | Priya | 88    |
| B     | Maya  | 69    |
| B     | Sam   | 74    |
| C     | Jon   | 55    |
+-------+-------+-------+

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

GROUP BY is the tool that turns per-row data into per-category summaries, letting you answer questions like "how many students are in each grade" or "what is the average marks per grade" directly inside SQL.

In this lesson, you learned how GROUP BY groups rows by a shared value, how to combine it with COUNT() and AVG(), and the strict rule about non-aggregated columns. Next, you will learn how to filter those grouped results using HAVING.