What is the Difference between ORDER and GROUP BY?

Both the GROUP and ORDER BY clauses are used in organizing data. Find out which of the two is used to sort the data and which is used for counting and summing up.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.  You can get started using these free tools using my Guide Getting Started Using SQL Server.

How do GROUP 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 include 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 tend to be sorted; however, this isn’t a guarantee.