按计数排序

如果我有这样的表格和数据:

ID |  Name  |  Group


1    Apple     A


2    Boy       A


3    Cat       B


4    Dog       C


5    Elep      C


6    Fish      C

我希望按照从最小到最大的集团总价值来订购，例如: A-2记录 B-1记录 C-3记录所以它会变成:

3    Cat       B


1    Apple     A


2    Boy       A


4    Dog       C


5    Elep      C


6    Fish      C

我尽力了

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

但它只为我返回一个结果。

有什么提示吗? 谢谢。

249208 次浏览
小开

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

小开
最佳答案

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.

小开 
SELECT * FROM table
group by `Group`
ORDER BY COUNT(Group)
小开 
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
小开

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

小开

Try using below Query:

SELECT
GROUP,
COUNT(*) AS Total_Count
FROM
TABLE
GROUP BY
GROUP
ORDER BY
Total_Count DESC
小开

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
小开

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

提交答案