Back to list Standard SQL How to Order by Count in SQL? Database: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Operators:COUNT, ORDER BY, ASC, DESC, GROUP BY 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 Recommended courses: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: How to Sort Records with the ORDER BY Clause Tags: SQL MySQL MS SQL Server PostgreSQL Oracle SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.