SQL Order By Count

Solution 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.

Solution 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

Solution 3:

SELECT group, COUNT(*) FROM table GROUP BY group ORDER BY group

or to order by the count

SELECT group, COUNT(*) AS count FROM table GROUP BY group ORDER BY count DESC

Solution 4:

Try :

SELECT count(*),group FROM table GROUP BY group ORDER BY group

to order by count descending do

SELECT count(*),group FROM table GROUP BY group ORDER BY count(*) DESC

This will group the results by the group column returning the group and the count and will return the order in group order

Solution 5:

SELECT * FROM table 
group by `Group`
ORDER BY COUNT(Group)