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

idmoviecitycountrygross
1Beau Is AfraidAmsterdamNetherlands483,754.22
2The French DispatchValenciaSpain385,741.59
3Beau Is AfraidValenciaSpain40,874.59
5Beau Is AfraidRotterdamNetherlands352,147.77
6The French DispatchDen HaagNetherlands100,524.14
8The French DispatchBarcelonaSpain354,789.52
9Beau Is AfraidDen HaagNetherlands208,471.52
10The French DispatchAmsterdamNetherlands408,974.56
11Beau Is AfraidBarcelonaSpain205,487.23
12The French DispatchRotterdamNetherlands236,974.54

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.

movie
Beau Is Afraid
The French Dispatch

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:

moviegross_per_movie
The French Dispatch2,332,130.33
Beau Is Afraid1,438,609.89

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:

gross_by_city
892,728.78
560,276.75
308,995.66
993,000.54
589,122.31
426,616.18

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:

city
Den Haag
Valencia
Barcelona
Rotterdam
Amsterdam

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:

citycountryaverage_gross
AmsterdamNetherlands446,364.39
BarcelonaSpain280,138.38
Den HaagNetherlands154,497.83
RotterdamNetherlands294,561.16
ValenciaSpain213,308.09

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:

citycountryaverage_gross
AmsterdamNetherlands446,364.39
Den HaagNetherlands154,497.83
RotterdamNetherlands294,561.16
BarcelonaSpain280,138.38
ValenciaSpain213,308.09

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:

citygross_per_city
Amsterdam892,728.78

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!