MySQL GROUP BY: Definition, Syntax, and Examples

It is often used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), or MAX() to perform operations on groups of data.
It organizes the result set into groups based on one or more columns.

GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name;

column_name → The column(s) to group by.
aggregate_function → Functions like COUNT(), SUM(), AVG(), MIN(), or MAX().
WHERE → Filters rows before grouping.

MySQL GROUP BY Examples

We have two tables:

Below is a selection from the "Students" table in the school_db database:

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

courses Table

course_name	course_name
101	Math
102	Science
103	History
104	English

The following SQL statement find total students in each course.

Example

SELECT city, COUNT(*) AS total_students

FROM students

GROUP BY city;

Try it yourself

Output:

city	total_students
Delhi	3
Mumbai	3
Bangalore	1

Explanation:

The COUNT(*) function counts the number of students in each city..
GROUP BY city groups the data by city.

GROUP BY With JOIN Example

The following SQL statement finds total score of students with score above 70 by course.

Example

SELECT c.course_name, SUM(s.score) AS total_score

FROM students s

JOIN courses c ON s.course_id = c.course_id

WHERE s.score > 70

GROUP BY c.course_name;

Try it yourself

Output:

course_name	total_score
Math	85
Science	95
History	170

Explanation:

SUM(score) calculates the total score of students scoring above 70.
WHERE filters data before applying GROUP BY.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.