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.

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