PostgreSQL ORDER BY COUNT

by

As a database consultant, I’ve found that adequately ordering query results by count is one of the most excellent data analysis methods. In this guide, I’ll explain everything you need to know about using PostgreSQL’s ORDER BY with the COUNT function.

Postgresql order by count

In Postgresql, COUNT is a function that gives us the number of rows that match a particular query condition.

Syntax

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
ORDER BY COUNT(*) DESC;

Method 1: ORDER BY COUNT

We’ll group the results by country and count the number of employee IDs from each country. We can also use COUNT to get the aggregate values. We will also sort the groups in descending order by the number of employees. That way, the countries with the most users will appear at the top. Let’s check the query.

SELECT address, COUNT(id) FROM employee GROUP BY address ORDER BY COUNT(id) DESC ;

After executing the above query, I got the expected output, as shown in the screenshot below.

Postgresql order by count

Read PostgreSQL CASE with Examples

Method 2: ORDER BY COUNT with Aliases

One of my favorite approaches is using column aliases for the query below.

SELECT product_name, COUNT(*) AS purchase_count
FROM electronics
GROUP BY product_name
ORDER BY purchase_count DESC
LIMIT 3;

After executing the above query, I got the expected output, as shown in the screenshot below.

ORDER BY COUNT with Aliases

Method 3: Using ORDER BY Position Instead of Alias

We can use the query below for this purpose.

SELECT product_name, COUNT(*) 
FROM electronics
GROUP BY product_name
ORDER BY 2 DESC;

“ORDER BY 2” refers to the second column in the SELECT statement (our COUNT result).

Using ORDER BY Position Instead of Alias

Method 4: ORDER BY Multiple COUNT Expressions

We can order by multiple aggregations using the query below.

SELECT product_name, 
       COUNT(DISTINCT product_id) AS unique_products,
       COUNT(*) AS total_purchases
FROM electronics
GROUP BY product_name
ORDER BY COUNT(DISTINCT product_id) DESC, COUNT(*) DESC;

After executing the above query, I got the expected output, as shown in the screenshot below.

ORDER BY Multiple COUNT Expressions

Method 5: ORDER BY COUNT with Multiple Grouping Fields

Sometimes, you need more insights. Let’s say you want to analyze product performance by category.

SELECT category, product_name, COUNT(*) AS purchase_count
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY category, product_name
ORDER BY category, COUNT(*) DESC;

Optimizing Performance for ORDER BY COUNT Queries

When dealing with large datasets, performance becomes critical. Here are my top optimization tips:

  1. Create appropriate indexes: Add indexes on columns used in GROUP BY clauses
  2. Limit results when possible: Use LIMIT when you only need top results
  3. Partition large tables: Improve performance by reducing scan size

Conclusion

Effectively using PostgreSQL’s ORDER BY with COUNT functions is needed for the best sorting result. By following the methods outlined in this article, you can use the PostgreSQL ORDER BY COUNT expression at its fullest potential and get the best sorting order for your data.

You may also like the articles below.