Using GROUP BY and ORDER BY Together: A Guide

Untangling how to use two basic SQL clauses – GROUP BY and ORDER BY – in one query.

GROUP BY and ORDER BY are basic SQL clauses. As such, they are taught relatively early in the learning process. And they usually don’t present a big hurdle to beginners. However, using GROUP BY and ORDER BY together can be confusing. By the end of this article, your confusion on this subject will go away.

If you want hands-on practice using GROUP BY with ORDER BY, our interactive SQL Basics course will help. As the name says, it covers the SQL basics. It makes it easier to follow the topics we’re about to present in the article.

The course itself has 129 exercises for practicing basic SQL concepts. These concepts include pulling data from one or multiple tables (JOINs) and filtering data using WHERE and operators such as LIKE, ILIKE, IN, and BETWEEN. Of course, GROUP BY is there, as are the set operations UNION, INTERSECT, and EXCEPT.

What Are GROUP BY and ORDER BY in SQL?

GROUP BY and ORDER BY are not the same – that’s obvious!

The SQL GROUP BY clause groups rows of data with the same values in the specified columns. This clause is most commonly used with SQL aggregate functions to compute statistics (such as a count of certain values, sum, average, and the minimum/maximum value in a set) for a group of rows. Here’s a simple example:

SELECT 
  country,
  COUNT(*)
FROM box_office
GROUP BY country;

The query asks the database to group the data by the values in the country column. Then it shows the country name and counts the rows. This will tell us how many records from the table belong to each country.

Now, let’s talk about ORDER BY. This command sorts the query output in ascending (1 to 10, A to Z) or descending (10 to 1, Z to A) order. The ascending sort is the default; if you omit the ASCending or DESCending keyword, the query will be sorted in ascending order. You can specify the sort order using ASC or DESC. Here’s a simple example:

SELECT 
  movie,
  city,
  gross
FROM box_office
ORDER BY gross;

This query selects the movie name, the city where the movie is showing, and the gross earnings. Then the output is sorted by gross earnings using the ORDER BY clause.

This is something you probably know. Now, let’s see how these SQL clauses can be used together. It might be helpful to have this article on how GROUP BY works or the guide on ORDER BY handy, just in case you need clarification on something we mention in this article.

Example Data

We’ll use a table named box_office in the following examples. You can create it using this query.

It’s an overview of two movies’ box office earnings: ‘Beau Is Afraid’ and ‘The French Dispatch’.

The data shows the  earnings (in US dollars) from cinemas in two countries – Spain and the Netherlands – and their respective cities: Amsterdam, Rotterdam, Den Haag, Barcelona, Madrid, and Valencia.

GROUP BY and ORDER BY an Unaggregated Column in SELECT

Let’s start with the easiest example of using GROUP BY and ORDER BY in one query. We want to show the list of movies in alphabetical order.

Here’s how to group by one column and order the result by one column:

SELECT movie
FROM box_office
GROUP BY movie
ORDER BY movie ASC; 

The query selects the movie name from the table box_office. We then need to use GROUP BY. Without that, the output would be all twelve rows from the table, showing the repeating names of the same two films from the table box_office. We want each movie to appear only once, as this is the whole point of the list. Grouping data is done simply by specifying the column you want to group by in the GROUP BY clause.

The output is ordered using the ORDER BY clause. As with GROUP BY, we specify the column name in the clause. We want the output to be sorted alphabetically, so the column name is followed by ASC.

Let’s use this image of the SQL query execution order to visualize how the database arrives at the result.

The query first gets the table in the FROM clause. Then it groups data according to the specified column in GROUP BY. After that, it selects the column in the SELECT statement. And only after all that is the output sorted by the ORDER BY clause.

In other words, data is first grouped, then sorted. This leads us to an important note: When using both GROUP BY and ORDER BY in one query, GROUP BY must always, without exception, be written before ORDER BY! 

Let’s take a look at the query’s output.

As you can see, it’s a list showing our two movies in alphabetical order.

GROUP BY and ORDER BY an Aggregate Column in SELECT

You can use GROUP BY together with ORDER BY when ordering by an aggregate function shown in the SELECT. Here’s an example:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY gross_per_movie DESC;

The query selects a movie from the table. Then we use the aggregate function SUM() to sum the movie’s gross earnings. Since we want to show the gross earnings per movie, we need to group the output by movie name. As a final step, we want to sort the output from the highest to the lowest earnings. We can do that by listing the aggregated column in ORDER BY. This must be followed by the keyword DESC to sort from highest to lowest.

Note that we use the alias gross_per_movie for the aggregated column in ORDER BY. We could have also used the full aggregate expression:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY SUM(gross) DESC;

Here’s how the output looks:

It shows two movies and their earnings in descending order.

GROUP BY And ORDER BY an Unaggregated Column Not in SELECT

In the first example, we ordered the output by the column shown in the SELECT. Let’s now try to order data by a column that is not in the SELECT:

SELECT SUM(gross) AS gross_by_city
FROM box_office
GROUP BY city
ORDER BY city ASC;

In this query, we use the SUM() function to calculate the gross earnings by city. However, there’s no city column in SELECT. That’s because we just want to see the gross values. We still group the output by the city, the same way as we would if this column is in SELECT. Finally, we order the output alphabetically by city.

The output is shown below:

As you can see, the query works. Remember: You can order the output by an unaggregated column not shown in SELECT – but only if that column is listed in GROUP BY.

This means that data can also be grouped by a column not shown in SELECT. Can the column be listed in SELECT but not in GROUP BY? No! You should remember this rule: If the unaggregated column appears in SELECT, it must also appear in GROUP BY.

GROUP BY And ORDER BY an Aggregated Column Not in SELECT

Let’s tweak the above example a little and try to order data by an aggregated column not listed in the SELECT.

Here’s the code. It attempts to show a list of cities ordered by gross earnings:

SELECT city
FROM box_office
GROUP BY city
ORDER BY SUM(gross);

We select the column city and group the output by it.

Then we use SUM() in ORDER BY so that we can sort the output by the earnings per city. We sort it ascendingly.

Let’s see if the query works:

Yup, it does! Here’s another note: You can order the output by an aggregated column not shown in the SELECT.

GROUP BY And ORDER BY Multiple Columns

So far, we’ve grouped data by only one column. Let’s see how you can group data by more than one column.

Let’s show each city and its country:

SELECT 
  city,
  country,
  AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY city;

We select the city and country. After that, we use AVG() to calculate the average gross earnings by city. Then we list the same columns in GROUP BY. As you learned, every column appearing in SELECT must also appear in GROUP BY. When we list multiple columns in GROUP BY, we simply separate them with a comma.

Finally, we sort the output by city in ascending order:

This begs the question: If you can GROUP BY multiple columns, can you also ORDER BY more than one column?

Let’s try:

SELECT city,
	 country,
	 AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY country, city;

The principle is the same as in GROUP BY: just list the columns in ORDER BY and separate them by a comma.

Here’s what the query returns:

Yes, the output is really ordered by the country and then by each city within the country.

Aggregated Column in Both GROUP BY And ORDER BY

We’ve tried all the combinations, but we didn’t try to GROUP BY and ORDER BY multiple columns, of which one is aggregated. Let’s try that:

SELECT 
  city,
  AVG(gross) AS average_gross_by_city
FROM box_office
GROUP BY city, AVG(gross)
ORDER BY city, AVG(gross);

This query wants to show the average gross earning by city. We list the city in the SELECT and use the AVG() function.  Then we group by city and the aggregated column. Finally, the output is sorted by the same column.

However, the only output we get is this error.

The error message literally means what it says: aggregate functions are not allowed in GROUP BY. What would even be the point of using them in GROUP BY? The aggregate function aggregates data to one row, and GROUP BY can’t do anything more with it.

Using GROUP BY With HAVING And ORDER BY

GROUP BY and ORDER BY are not the only clauses that can be used in one query – we can also add HAVING.

Let’s inspect the following code to see how this works.

SELECT 
  city,
  SUM(gross) AS gross_per_city
FROM box_office
GROUP BY city
HAVING SUM(gross) > 800000
ORDER BY SUM(gross) > 800000 DESC;

This query should:

• Return cities with gross earnings above $800,000.
• Show the amount earned by that city.
• Order the output by gross earning in descending order.

To do that, we select the column city and use the SUM() function. Then we group the output by city. After that, we use the HAVING clause to show only cities with earnings over $800,000. We simply use the aggregate column from SELECT and set it against the condition using the '>' comparison operator. Then we use the same in ORDER BY to sort the output descendingly.

The rule here is: The HAVING clause is always written after GROUP BY but before ORDER BY.

You can revisit the SQL query execution order image to review this. You’ll see that HAVING is executed after GROUP BY but before ORDER BY. In other words, the data is first grouped, then filtered, and finally ordered.

Here’s the query’s result:

You see there are only two cities where our two movies earned more than $800,000: Amsterdam and Madrid.

Practice Using GROUP BY with ORDER BY

This article showed you that, yes, GROUP BY and ORDER BY can be used in the same SQL query. Not only that, but they can be used in multiple ways.

Along the way, we learned some important rules:

1. GROUP BY comes before ORDER BY.
2. Output can be ordered by an unaggregated column not shown in SELECT only if the column appears in GROUP BY.
3. Data can be grouped by the unaggregated column not shown in SELECT.
4. Any unaggregated column appearing in SELECT must be listed in GROUP BY.
5. The output can be ordered by an aggregated column not shown in SELECT.
6. An aggregated column is not allowed in GROUP BY.
7. HAVING comes after GROUP BY but before ORDER BY.

While we’ve explained these rules and you feel you’ve learned something, this is not enough.

To retain this knowledge, you need practice using GROUP BY and ORDER BY – especially in the beginning of your SQL journey. Practice in SQL means writing lots of code. This is the only way to consolidate your knowledge. Our interactive SQL Basics course offers 129 hands-on SQL exercises; that’s a lot of room to practice!