SQL Order By Count

Question

If 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 total of Group from smallest to largest value, such as : A - 2 records , B - 1 record , C - 3 records , so it will become:

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.

Are there any hints? Thank you.

Answer 1

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.