How to Group by Multiple Columns in MySQL

Preet Sanghavi Feb 02, 2024
  1. the Group By Statement in MySQL
  2. the GROUP BY Statement in MySQL With Multiple Columns
How to Group by Multiple Columns in MySQL

In this tutorial, we aim to understand how to use the GROUP BY command with two or more columns.

The MySQL GROUP BY command is a technique by which we can club records together with identical values based on particular criteria defined for the purpose of grouping. When we try to group data considering only a single column, all the records that possess the same values on which the criteria is defined are coupled together in a single output.

However, MySQL enables users to group data not only with a singular column for consideration but also with multiple columns. We will explore this technique in the latter section of this tutorial. To summarize, when we try to group by considering multiple columns, we can get a result wherein the grouping of column values is done concerning more than one column along with a grouping criteria.

While this concept might sound difficult to implement, let us start off by considering one column in our GROUP BY statement.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_grade int,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_grade,stu_lastName) 
 VALUES(1,"Preet",40,"Sanghavi"),
 (2,"Rich",50,"John"),
 (3,"Veron",60,"Brow"),
 (4,"Geo",70,"Jos"),
 (5,"Hash",80,"Shah"),
 (6,"Sachin",90,"Parker"),
 (7,"David",25,"Miller"),
 (8,"Richa",50,"Joh"),
 (9,"Verona",60,"Brow"),
 (10,"Geoa",70,"Josh"),
 (11,"Hasha",80,"Ash"),
 (12,"Allen",90,"Parker");

The above query creates a table along with rows with student first name and last name in it. In order to view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output:

stu_id	stu_firstName	stu_grade	stu_lastName
1			Preet			40			Sanghavi
2			Rich			50			John
3			Veron			60			Brow
4			Geo				70			Jos
5			Hash			80			Shah
6			Sachin			90			Parker
7			David			25			Miller
8			Richa			50			Joh
9			Verona			60			Brow
10			Geoa			70			Josh
11			Hasha			80			Ash
12			Allen			90			Parker

Now, let us try to understand the usage of a simple GROUP BY statement to group stu_lastName with the sum of stu_grade as the aggregate clause.

the Group By Statement in MySQL

As we know, the GROUP BY statement helps us group records that possess the same values for the defined criteria. The basic syntax of the GROUP BY statement is as follows:

SELECT col_a, col_b, aggregate_function(col_c)
FROM tab_name
WHERE condition GROUP BY criteria_col_1;

We can find the sum of scores for students with the same surname as follows:

SELECT SUM(stu_grade), stu_lastName FROM student_details
GROUP BY stu_lastName;

The aforementioned code gives the following output:

SUM(stu_grade)	stu_lastName
40				Sanghavi
50				John
120				Brow
70				Jos
80				Shah
180				Parker
25				Miller
50				Joh
70				Josh
80				Ash

the GROUP BY Statement in MySQL With Multiple Columns

As seen above, MySQL provides the functionality to group records based on a criterion. One other addition to this method is that we can group more than one column from the table at a time. The syntax for grouping more than one column in MySQL can be written as follows:

SELECT col_a, col_b, aggregate_function(col_c)
FROM tab_name
WHERE condition GROUP BY criteria_col_1, criteria_col_2, criteria_col_3;

As we can see above, criteria_col_1, criteria_col_2 and criteria_col_3 are the three columns included in the GROUP BY clause.

Now let us try to group students’ first and last names based on the sum of their grades as the aggregate function. We can perform this operation with the following code:

SELECT SUM(stu_grade), stu_lastName, stu_firstName
FROM student_details
GROUP BY stu_lastName, stu_firstName;

The output of the code above is as follows:

SUM(stu_grade)	stu_lastName	stu_firstName
40				Sanghavi		Preet
50				John			Rich
60				Brow			Veron
70				Jos				Geo
80				Shah			Hash
90				Parker			Sachin
25				Miller			David
50	            Joh	            Richa
60				Brow			Verona
70				Josh			Geoa
80				Ash				Hasha
90				Parker			Allen

As we can see, the output groups both the columns stu_firstName and stu_lastName. Similarly, we can group multiple columns in MySQL. Therefore, the GROUP BY statement can be used efficiently with one or multiple columns with the methods mentioned above.

Preet Sanghavi avatar Preet Sanghavi avatar

Preet writes his thoughts about programming in a simplified manner to help others learn better. With thorough research, his articles offer descriptive and easy to understand solutions.

LinkedIn GitHub

Related Article - MySQL Groupby