JIYIK CN >

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

MySQL GROUP BYcommand is a technique by which we can group records having same values ​​together based on a specific criteria defined for grouping purpose. When we try to group the data considering only a single column, all the records having same value for the defined criteria are coupled together in a single output.

However, MySQL enables users to group data not only using a single column for consideration, but also using multiple columns for grouping. We will explore this technique later in this tutorial. To summarize, when we try to group by considering multiple columns, we can get a result where the grouping of column values ​​is done for multiple columns as well as grouping criteria.

While this concept may sound difficult to implement, let's first consider GROUP BYa column in a statement.

Before we get started, however, we create a dummy dataset to work with. Here, we create a table, student_details, and 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 query above creates a table and rows containing the first and last names of students. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code will 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 sum of with as aggregate clause using simple GROUP BYstatement .stu_lastNamestu_grade

Group ByStatements in MySQL

As we know, GROUP BYthe statement helps us to group the records having the same values ​​for the defined criteria. GROUP BYThe basic syntax of the 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 of students with the same surname as follows:

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

The above 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

GROUP BYStatements for multiple columns in MySQL

As shown above, MySQL provides the ability to group records based on criteria. Another addition to this approach is that we can group multiple columns in a table at once. The syntax for grouping multiple columns 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_2and criteria_col_3are GROUP BYthe three columns included in the clause.

Now let's try to group the first and last names of students based on the sum of their grades as an aggregate function. We can do this using the following code:

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

The output of the above code 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 the columns stu_firstNameand stu_lastName. Similarly, we can group multiple columns in MySQL. Hence, GROUP BYthe statement can be effectively used for one or more columns using the above method.