Back to cookbooks list Articles Cookbook

How to Order by Count in SQL?

Problem:

You aggregated data into groups, but you want to sort the records in descending order by the number of elements in the groups.

Example:

Our database has a table named user with data in the following columns: id, first_name, last_name, and country.

idfirst_namelast_namecountry
1LisaWilliamsEngland
2GaryAndersPoland
3TomWilliamsPoland
4MichaelBrownFrance
5SusanSmithUSA
6AnneJonesUSA
7EllieMillerPoland

Let’s create a report on our users. We’ll group the results by country and count the number of users from each country. But we’ll also sort the groups in descending order by number of users. That way, the countries with the greatest number of users will appear at the top.

Download SQL Basics Cheat Sheet

Solution:

SELECT country,
 COUNT(id) 
FROM user
GROUP BY  country
ORDER BY COUNT(id) DESC ;
countrycount(id)
Poland3
USA2
England1
France1

Discussion:

To sort the selected records by the number of the elements in each group, you use the ORDER BY clause.

The first step is to use the GROUP BY clause to create the groups (in our example, we group by the country column). Then, in the ORDER BY clause, you use the aggregate function COUNT, which counts the number of values in the column of your choice; in our example, we count distinct IDs with COUNT(id). This effectively counts the number of elements in each group. The ORDER BY clause then sorts the groups according to that computation.

As usual, you can use both ascending or descending order with ORDER BY. If you want descending order (as in this example), you use the DESC keyword. Ascending order doesn't need any keyword because it’s the default, but you can use the ASC keyword if you want to be explicit. This is the same example but with results sorted in ascending order:

Solution:

SELECT country,
 COUNT(id) 
FROM user
GROUP BY  country
ORDER BY COUNT(id);

Here’s the result:

countrycount(id)
England1
France1
USA2
Poland3

Recommended courses:

Recommended articles: