Order by COUNT per value

sql

join

prompts-engineering

best-practices

byAnton Shumikhin·Dec 27, 2024

Here's how to quickly sort records based on their frequency using the COUNT aggregate and GROUP BY clause:

-- Sure, databases do a "COUNT", but can they count ceiling tiles during a boring meeting?
SELECT your_column, COUNT(*) AS frequency
FROM your_table
-- It's not you, it's me. Just kidding, it's you.
GROUP BY your_column
-- Houston, we have sort of... sorts.
ORDER BY frequency DESC;

For a more detailed list containing the full dataset and maintaining sort order, pair an inner join with a count subquery:

SELECT a.*, b.frequency
FROM your_table a
-- I'm tired of being 'left'... let's take it to a new level, how about an 'inner' join?
JOIN (
    SELECT your_column, COUNT(*) AS frequency
    FROM your_table
    GROUP BY your_column
) b ON a.your_column = b.your_column
-- What do we say to bad order? Not today!
ORDER BY b.frequency DESC;

Be sure to replace your_column and your_table with your actual identifiers.

For enhanced interactivity, consider the following pointers:

1. Filtering sparse records

To eliminate scarce occurrences, introduce the HAVING clause:

-- Go big or go home.
SELECT your_column, COUNT(*) AS frequency
FROM your_table
GROUP BY your_column
HAVING COUNT(your_column) > X
ORDER BY frequency DESC;

Update X with the minimum count to pass the filter.

2. Applying pagination

Breakdown large datasets using LIMIT and OFFSET for easier readability:

-- Paging Dr. SQL...
SELECT your_column, COUNT(*) AS frequency
FROM your_table
GROUP BY your_column
ORDER BY frequency DESC
LIMIT Y OFFSET Z;

Replace Y with your number of results per page and Z with your desired offset value.

Diving deeper into COUNT()

3. Conditional counting

Who said counting is boring? Give it a twist by classifying your data into distinct categories with CASE statements:

-- Let's see how many turn up at my SQL party.
SELECT your_column,
       SUM(CASE WHEN condition_1 THEN 1 ELSE 0 END) AS count_1,
       SUM(CASE WHEN condition_2 THEN 1 ELSE 0 END) AS count_2
FROM your_table
GROUP BY your_column
ORDER BY count_1 DESC, count_2 DESC;

4. Merging additional intelligence

Combine count data into other tables for a comprehensive insight. Utilise JOIN operations to link and exhibit extended details:

-- It's going to be legen...wait for it...aggregate! 
SELECT c.*, t.frequency
FROM categories c
JOIN (
    SELECT category_id, COUNT(*) AS frequency
    FROM products
    GROUP BY category_id
) t ON c.id = t.category_id
ORDER BY t.frequency DESC;

5. Wrapping around Time

Group by date intervals for time-series analysis using date functions:

-- Time... is on my side, yes it is!
SELECT DATE_FORMAT(your_date_column, '%Y-%m') AS year_month, COUNT(*) AS frequency
FROM your_table
GROUP BY year_month
ORDER BY year_month DESC;

6. Live streaming with views

Why not wrap all the logic into a VIEW for live visual analytics? It's easier.

-- Sometimes, you just gotta 'view' things from a different 'table'. 
CREATE VIEW view_name AS
SELECT your_column, COUNT(*) AS frequency
FROM your_table
GROUP BY your_column;