GROUP BY vs. ORDER BY

This article explains the complete overview of the GROUP BY and ORDER BY clause. They are mainly used for organizing data obtained by SQL queries. The difference between these clauses is one of the most common places to get stuck when learning SQL. The main difference between them is that the GROUP BY clause is applicable when we want to use aggregate functions to more than one set of rows. The ORDER BY clause is applicable when we want to get the data obtained by a query in the sorting order. Before making the comparison, we will first know these SQL clauses.

ORDER BY Clause

The ORDER BY clause is used in SQL queries to sort the data returned by a query in ascending or descending order. If we omit the sorting order, it sorts the summarized result in the ascending order by default. The ORDER BY clause, like the GROUP BY clause, could be used in conjunction with the SELECT statement. ASC denotes ascending order, while DESC denotes descending order.

The following is the syntax to use the ORDER BY clause in a SQL statement:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Let us understand how the ORDER BY clause works with the help of the following example. Suppose we have a table developer that contains the following data:

GROUP BY vs. ORDER BY

We can see that these results are not displayed in an organized way. Suppose we want to organize results in ascending or descending order based on the state column. In that case, we would need the ORDER BY command to get the desired result. We can do this by executing the command as follows:

mysql> SELECT D_name, D_state, D_salary
FROM developers
ORDER BY D_state ASC;

Here is the output where we will get the desired results:

GROUP BY vs. ORDER BY

GROUP BY Clause

The GROUP BY clause is used in SQL queries to organize data that have the same attribute values. Usually, we use it with the SELECT statement. It is always to remember that we have to place the GROUP BY clause after the WHERE clause. Additionally, it is paced before the ORDER BY clause.

We can often use this clause in collaboration with aggregate functions like SUM, AVG, MIN, MAX, and COUNT to produce summary reports from the database. It's important to remember that the attribute in this clause must appear in the SELECT clause, not under an aggregate function. If we do so, the query would be incorrect. As a result, the GROUP BY clause is always used in conjunction with the SELECT clause. The query for the GROUP BY clause is grouped query, and it returns a single row for each grouped object.

The following is the syntax to use GROUP BY clause in a SQL statement:

SELECT column_name, function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

Let us understand how the GROUP BY clause works with the help of an example. Here we will demonstrate it with the same table.

Suppose we want to know developer's average salary in a particular state and organize results in descending order based on the state column. In that case, we would need both the GROUP BY and ORDER BY command to get the desired result. We can do this by executing the command as follows:

mysql> SELECT D_state, avg(D_salary) AS salary
FROM developers
GROUP BY D_state
ORDER BY D_state DESC;

This query initially formed an intermediate result that has grouped the state. Next, the AVG function is performed on each group of states, then sort the result in descending order, and finally, we will get the desired results as shown below:

GROUP BY vs. ORDER BY

Key Differences between GROUP BY and ORDER BY

The following are the key distinctions between the Group By and Order By clause.

The Group By clause is used to group data based on the same value in a specific column. The ORDER BY clause, on the other hand, sorts the result and shows it in ascending or descending order.
It is mandatory to use the aggregate function to use the Group By. On the other hand, it's not mandatory to use the aggregate function to use the Order By.
The attribute cannot be under GROUP BY statement under aggregate function, whereas the attribute can be under ORDER BY statement under aggregate function.
Group By clause controls the presentation of tuples that means grouping is done based on the similarity among the row's attribute values. In contrast, the ORDER BY clause controls the presentation of columns that means the ordering or sorting is done based on the column's attribute values either in ascending or descending order.
GROUP BY is always placed after the WHERE clause but before the ORDER BY statement. On the other hand, ORDER BY is always used after the GROUP BY statement.