Search code examples
sqlmysqlsql-order-by

Order results by COUNT without GROUP BY

I have a table and data like this:

ID | Name  | Group
1  | Apple | A
2  | Boy   | A
3  | Cat   | B
4  | Dog   | C
5  | Elep  | C
6  | Fish  | C

and I wish to order it according to the count of items in group from smallest to largest value, such as: B - 1 record, A - 2 records, C - 3 records, so it will become:

ID | Name  | Group
3  | Cat   | B
1  | Apple | A
2  | Boy   | A
4  | Dog   | C
5  | Elep  | C
6  | Fish  | C

I tried:

$sql = "SELECT ID, Name
    FROM table
    ORDER BY COUNT(Group)";

but it just returns one result for me.

How do I order the data as described?

Solution

  • You need to aggregate the data first, this can be done using the GROUP BY clause:

    SELECT Group, COUNT(*)
FROM table
GROUP BY Group
ORDER BY COUNT(*) DESC

    The DESC keyword allows you to show the highest count first, ORDER BY by default orders in ascending order which would show the lowest count first.