MySQL | GROUP BY
Start your free 7-days trial now!
MySQL's GROUP BY clause allows you to group identical entries in a column into groups. GROUP BY will return one record for each group.
Syntax
SELECT column_name(s)FROM table_nameWHERE conditonGROUP BY column_nameORDER BY column_name;
Examples
Consider the following table about whether students have a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
Use with aggregate functions
We can use GROUP BY with aggregate methods such as COUNT, MAX, MIN, SUM, AVG to effectively return information about our table.
To return the number of students for each age:
SELECT age, COUNT(*) FROM drivers_licenseGROUP BY age;
+------+----------+| age | COUNT(*) |+------+----------+| 17 | 2 || 19 | 1 || 18 | 1 || 16 | 1 |+------+----------+
To check the average age of students with and without a license:
SELECT has_license, AVG(age) FROM drivers_licenseGROUP BY has_license;
+-------------+----------+| has_license | AVG(age) |+-------------+----------+| 0 | 17.0000 || 1 | 18.0000 |+-------------+----------+
The average age for students without a license is 17 and students with a license is 18.
To check for minimum and maximum age of students with and without a license:
SELECT has_license, MIN(age), MAX(age) FROM drivers_licenseGROUP BY has_license;
+-------------+----------+----------+| has_license | MIN(age) | MAX(age) |+-------------+----------+----------+| 0 | 16 | 18 || 1 | 17 | 19 |+-------------+----------+----------+
