How do GROUP BY and ORDER BY Differ?
This is a question I’m frequently asked. On the surface, both clauses appear to do the same thing; that is sort sort data. But this is where their similarities end. In fact, both serve entirely different purposes.
- The
ORDER BY clause’s purpose is to sort the query result by specific columns. - The
GROUP BY clause’s purpose is summarize unique combinations of columns values.
Before we get into their differences, consider the general setup of the SELECT statement:
SELECT columnlist
From table
GROUP BY columnA, columnB
ORDER BY columnlist
Notice that the ORDER BY clause appears at the end. You can use this as a clue to understand that the ORDER BY statement is used to sort the final result of the query. In fact, it can be used to sort results from a GROUP BY clause. Confused? I was at first!
Ok, let’s break it down.
ORDER BY
The ORDER BY statement is used to sort values. You probably already knew that! So
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID
will sort the value, according to SalesOrderID.
Every row in the table is included in the result. The values are sorted in ascending order according to the SalesOrderID.
GROUP BY
Contrast this to the GROUP BY clause, which is used to group like column values into a single row.
This is useful as it allows you to summarize information. For instance, you can use aggregate functions such as SUM and AVERAGE to calculate values.
In this example:
SELECT SalesOrderID,
SUM(OrderQty* UnitPrice) As TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
We are grouping by SalesOrderID and summing each order’s product prices to return the total. This is the magic of the GROUP BY clause: it allows you to perform summary calculations on multiple rows.
With the GROUP BY clause, not every row is included in the result. Instead, only unique combinations of SalesOrderID along with the sum are included.
Now the ORDER BY and GROUP BY can be used together. You may ask what is the point, if the results are already grouped by SalesOrderID, but what about ordering by the total price? You can do this as:
SELECT SalesOrderID,
SUM(OrderQty* UnitPrice) As TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice
To summarize, the key difference between order by and group by is:
ORDER BY is used to sort a result by a list of columns or expressions.GROUP BY is used to create unique combinations of a list of columns that can be used to form summaries. A byproduct of this operation is that the grouping tends to be sorted; however, this isn’t a guarantee.
