SQL allows to organize the data obtained by the query. We have two clauses for organizing the data obtained from the query that are Group By and Order By clause. The point that distinguishes Group By and Order By clause is that Group By clause is used when we want to apply the aggregate function to more than one set of tuples and Order By clause is used when we want to sort the data obtained by the query.
Let us discuss some differences between Group By clause and Order By clause with the help of the comparison chart shown below.
Content: Group By Vs Order By
Comparison Chart
| Basis for Comparison | Group By | Order By |
|---|---|---|
| Basic | Group By is used to form the Group of the set of the tuples. | Order By is used to arrange the data obtained as a result of a query in Sorted form. |
| Attribute | Attribute under Aggregate function can not be in Group By clause. | Attribute under aggregate can be in Order By Clause. |
| Ground | Done on the ground of similarity among attribute values. | Done on the ground of ascending order and descending order. |
Definition of Group By Clause
Aggregate functions like avg, min, max, sum, count are applied to the single set of tuples. In case, if you want to apply the aggregate functions to the group of the set of tuples then we have Group by clause for that. Group by clause groups the tuples that have same attribute value.
There is one thing to remember about the Group By clause, be sure that the attribute under the Group By clause must appear in the SELECT clause but not under an aggregate function.
If the Group By clause contains an attribute that is not under SELECT clause or if it is under SELECT clause but under aggregate function then the query becomes erroneous. Hence, we can say that the Group By clause is always used in collaboration with the SELECT clause.
Let us take an example to understand Group By clause.
SELECT Department _ID, avg(Salary) as avg_salary from Teacher Group By Department_ID.
You can see that initially an intermediate result is formed that has grouped the departments.
Next, the aggregate function avg is applied to each group of departments, and the result is shown below.
Definition of Order By Clause
Order By clause is used to display data obtained by a query in the sorted order. Like Group By clause, Order By clause is also used in collaboration with the SELECT clause. If you do not mention the sorting order, Order By clause sorts data in the ascending order. You can specify ascending order as asc and descending order as desc.
Let us understand the working of Order By clause with the help of the following example. We have a Teacher table, and I will apply sorting to two columns Department_Id and Salary, of the Teacher table. Select Department_ID, Salary From Teacher Order By Department_Id asc, Salary desc.
You can see that first, it arranges the Department _ID in ascending order and then it arranges the salaries in the same department in descending order.
Key Differences Between Group By and Order By
- Group By clause groups the set of tuples in a relation that are under the SELECT clause. On the other hands, the Order By clause sort the result of the query in ascending or descending order.
- The attribute under aggregate function can not be under Group By clause whereas, the attribute under aggregate function can be there under Order By clause.
- Grouping of tuples is done on the basis of similarity among the attribute values of the tuples. On the other hands, the ordering or sorting is done on the basis of ascending order or descending order.
Conclusion
If you want to form the group of the set of tuples, then you must use Group By clause. In case you want to arrange the data of a single column or, more than one column in the set of tuples in ascending or descending order then Order By clause must be used.
Jazz says
Clear.