When someone starts learning MySQL or any other DBMS, one of the earliest places where the students get stuck is where to use group by and order by clause. Though order by clause is simple and straightforward, the group by is a little bit confusing to new learners.
In this tutorial, we will see detailed information about each one of them and compare them accordingly. So, let’s get started!
Introduction to GROUP BY and ORDER BY
The main reason why students get stuck on the difference between these two clauses is because of the lack of understanding of both concepts. If you notice, even if both clauses are used to arrange the data in a specific way, they differ from each other vastly.
Before jumping on the difference between GROUP BY and ORDER BY clauses, we will first see some important concepts about them so that you will understand the difference between them yourself.
Difference Between ORDER BY and GROUP BY
Here is a quick comparison between these two clauses on important points.
| ORDER BY | GROUP BY |
|---|---|
| It is used to sort the records in either ascending or descending order. | It is used to group together the records having the same values. |
| The order by clause comes after the group by and where clause. | The group by comes after the where clause but before the order by clause. |
| The order by clause can be used without any aggregate function. | It doesn’t make any sense to use the group by clause without any aggregate function. |
| The order by clause controls the presentation of columns rather than rows. | The group by clause controls the rows rather than columns, |
Understanding Group By and Order By
Even if you have learned both concepts, it is ok if you don’t understand them properly. We will see only important points here and skip those we think are less important.
Before that, we will need a table that consists of some records so that we can experiment with it.
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
subject VARCHAR(10),
name VARCHAR(100)
);
INSERT INTO students(subject,name)
VALUES("English","Yuji"),("Spanish","Yuji"),
("Hindi","Genos"),("English","John"),
("Marathi","Raj"),("Hindi","Raj"),("Spanish","Maya"),
("Hindi","Maya"),("English","Mira");Code language: SQL (Structured Query Language) (sql)
The above table is created without applying the normal forms. Think of it as an example table.
What is the ORDER BY Clause?
The ORDER BY clause is the simplest to understand. It simply sorts the table data in either ascending order or descending order using the keywords ASC (ascending) and DESC (descending). If the specified field you want to sort the records by is numerical then the sorting will be done by ascending count by default. If the field is of type string, the sorting will be done in alphabetical order.
Note that, you can specify as many fields as you want while using the ORDER BY clause. This is because let’s say there are two records of the same value. In this case, if you want to sort the records by another column value, then you can specify that column name also.
Remember that, the sorting is done in ascending order by default. You don’t need to write the ASC keyword explicitly.
Let’s take an example.
In the table that we have created just now, there are repetitions of student names and subjects.
Let’s sort the table data by the student names.
SELECT * FROM students ORDER BY name;Code language: SQL (Structured Query Language) (sql)
See the highlighted area. The name “Maya” came twice but has different subjects. But why does the record of id 7 come before the record of id 8 even after having the same name? Why didn’t it show us vice-versa?
It is because if two records have the same values, the record which comes first in the table will be shown first after the sorting.
Now, if you want to sort the table values by name. However, if the name is the same, you want to sort the records by the subject. This can be done by the following method-
SELECT * FROM students ORDER BY name, subject;Code language: SQL (Structured Query Language) (sql)
Now you can see, the order is changed from the previous result.
What is the GROUP BY Clause?
The GROUP BY clause is used to group together all the records that have the same values. You will see the GROUP BY clause is often used with aggregate functions such as SUM, AVG, MIN, MAX, etc.
For example, if there are thousands of records of students. You want the count of students enrolled in each subject. You can do this with the GROUP BY clause and the aggregate function COUNT.
Let’s take a simple example.
In the table that we created, there are multiple subjects and students are enrolled in the subjects. Let’s find the number of students enrolled in each subject.
SELECT subject, COUNT(name) AS count FROM students GROUP BY subject;Code language: PHP (php)
As you can see, we got the number of students enrolled in each subject.
Note that, you can use the WHERE clause before the GROUP BY clause but not after. MySQL provides HAVING CLAUSE to use with the GROUP BY clause.
Summary
In this tutorial, we have learned the difference between ORDER BY and GROUP BY clauses with some simple and important points. If you are a new learner, make sure you read the complete tutorial thoroughly and don’t miss any points.
References
MySQL official documentation on a group by and order by clause.