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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/