GROUP BY and ORDER BY ClausesIn SQL, we have two commonly used clauses that help us to manipulate data; Group By clause and Order By clause.
A Group By clause is used to arrange the identical data/records into groups and the Order By clause is used to sort the data in ascending or descending order.
Using the GROUP BY clause we can organize the data in a table into groups (based on a column) and perform required calculations on them.
This clause is often used with the aggregate functions such as MIN(), MAX(), SUM(), AVG(), and COUNT() etc.
It is often used with the SELECT statement, and it is placed after the WHERE clause or before the HAVING clause. If we use the Order By clause, the Group By clause should precede the Order By clause.
The aggregate_function() and the WHERE clause are optional in the above syntax.
Assume we have created a table named CUSTOMERS that contains records of customers such as NAME, AGE, ADDRESS, and SALARY etc., using the following CREATE statement −
Now, we are inserting values into this table using the INSERT statement as follows −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
In the SQL query below, we are using the GROUP BY clause to group the rows based on their salaries from the CUSTOMERS table and counting the number of records in each group −
| SALARY | COUNT(SALARY) |
|---|---|
| 2000.00 | 2 |
| 1500.00 | 1 |
| 6500.00 | 1 |
| 8500.00 | 1 |
| 4500.00 | 1 |
| 10000.00 | 1 |
Using the ORDER BY clause we can sort the records in a particular order.
We can sort the data in ascending (ASC) or descending (DESC) order using this clause.
It is generally used along with the SELECT statement and comes at the end of it.
The following SQL statement sorts the CUSTOMERS table data based on the SALARY column in descending order.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 7 | Muffy | 24 | Indore | 10000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
Following is the comparison between GROUP BY and ORDER BY clauses in SQL −
| GROUP BY | ORDER BY |
|---|---|
| GROUP BY clause is used to group the results based on the similar column values. | ORDER BY clause is used to sort the result-set in ascending or descending order. |
| GROUP BY clause is used with aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG(). | ORDER BY clause is not used with aggregate functions. |
| GROUP BY clause is used to group rows that have the same values into summary rows. | ORDER BY clause is used to sort the result-set either by ascending or descending order. |
| GROUP BY clause is used after WHERE clause in a SQL query. | ORDER BY clause is used after GROUP BY clause in a SQL query. |
| GROUP BY clause reduces the number of rows in the output. | ORDER BY clause does not reduce the number of rows returned by the query. |
GROUP BY and ORDER BY are both essential clauses in SQL that help organize and sort data. While GROUP BY groups rows that share a property so aggregate functions can be applied, ORDER BY simply sorts the results either ascendingly or descendingly based on one or more columns. Understanding how and when to use them is crucial for efficient data handling.