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.

Answer 2

...none of the other answers seem to do what the asker asked.

For table named 'things' with column 'group':

SELECT   things.*, counter.count FROM   things LEFT JOIN (   SELECT     things.group, count(things.group) as count   FROM     things   GROUP BY     things.group ) counter ON counter.group = things.group ORDER BY   counter.count ASC; 

which gives:

id | name  | group | count  --------------------------- 3  | Cat   | B     | 1 1  | Apple | A     | 2 2  | Boy   | A     | 2 4  | Dog   | C     | 3 5  | Elep  | C     | 3 6  | Fish  | C     | 3