SQL Order By Count
MysqlDatabaseSql Order-ByMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
...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 - Mysql
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 - Mysql
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 - Mysql
SELECT * FROM table
group by `Group`
ORDER BY COUNT(Group)
Solution 6 - Mysql
Try using below Query:
SELECT
GROUP,
COUNT(*) AS Total_Count
FROM
TABLE
GROUP BY
GROUP
ORDER BY
Total_Count DESC
Solution 7 - Mysql
Below gives me opposite of what you have. (Notice Group column)
SELECT
*
FROM
myTable
GROUP BY
Group_value,
ID
ORDER BY
count(Group_value)
Let me know if this is fine with you...
I am trying to get what you want too...
Solution 8 - Mysql
Q. List the name of each show, and the number of different times it has been held. List the show which has been held most often first.
event_id show_id event_name judge_id
0101 01 Dressage 01
0102 01 Jumping 02
0103 01 Led in 01
0201 02 Led in 02
0301 03 Led in 01
0401 04 Dressage 04
0501 05 Dressage 01
0502 05 Flag and Pole 02
Ans:
select event_name, count(show_id) as held_times from event
group by event_name
order by count(show_id) desc