Click here to Skip to main content
15,393,863 members
Articles / Database Development / SQL Server / SQL Server 2012
Technical Blog
Posted 23 Feb 2015

Stats

26.6K views
2 bookmarked

What is the Difference between GROUP BY and ORDER BY

Rate me:
Please Sign up or sign in to vote.
3.91/5 (4 votes)
23 Feb 2015MIT2 min read
What is the difference between GROUP BY and ORDER BY

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:

SQL
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

SQL
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:

SQL
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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
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/

Comments and Discussions

 
AnswerCODING Pin
Devid john30-Jul-18 7:22
professionalDevid john30-Jul-18 7:22 
Question[My vote of 1] very small to be an article Pin
Biswabid Rath9-Mar-15 8:40
MemberBiswabid Rath9-Mar-15 8:40 
AnswerRe: [My vote of 1] very small to be an article Pin
essentialSQL11-Mar-15 1:59
MemberessentialSQL11-Mar-15 1:59 
AnswerRe: [My vote of 1] very small to be an article Pin
BillW3316-Mar-15 10:52
professionalBillW3316-Mar-15 10:52 
QuestionShould state some things more clearly Pin
Christophe Van Olmen26-Feb-15 1:36
professionalChristophe Van Olmen26-Feb-15 1:36 
AnswerRe: Should state some things more clearly Pin
essentialSQL4-Mar-15 16:15
MemberessentialSQL4-Mar-15 16:15 
GeneralExplained well Pin
Lucian Todor25-Feb-15 1:30
MemberLucian Todor25-Feb-15 1:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.