search
Search
Login
Map of Data Science
menu
menu search toc more_vert
Robocat
Guest 0reps
Sign up
Log in
account_circleMy Profile homeAbout paidPricing
emailContact us
exit_to_appLog out
Map of data science
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
check_circle
Mark as learned
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

MySQL | GROUP BY

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Statements and Clauses
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more
map
Check out the interactive map of data science

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_name
WHERE conditon
GROUP BY column_name
ORDER 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_license
GROUP 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_license
GROUP 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_license
GROUP BY has_license;
+-------------+----------+----------+
| has_license | MIN(age) | MAX(age) |
+-------------+----------+----------+
| 0 | 16 | 18 |
| 1 | 17 | 19 |
+-------------+----------+----------+
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!