MySQL SQL 按计数排序

SQL Order By Count

提问by Irene Ling

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:

我希望按照 Group 的总数从小到大排序，例如： A - 2 记录 ， B - 1 记录 ， C - 3 记录，所以它会变成：

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.

有什么提示吗？谢谢你。

回答by Vince Pergolizzi

You need to aggregate the data first, this can be done using the GROUP BY clause:

您需要先聚合数据，这可以使用 GROUP BY 子句完成：

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.

DESC 关键字允许您首先显示最高计数，ORDER BY 默认按升序排序，这将首先显示最低计数。

回答by trapper

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

回答by xxjjnn

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

...其他答案似乎都没有按照提问者的要求行事。

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

对于名为 'things' 且列 '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

回答by Manse

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 groupcolumn returning the groupand the countand will return the order in grouporder

这将按group返回group和的列对结果进行分组count，并将按顺序返回group顺序

回答by juergen d

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

回答by Fahim Parkar

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

我也在努力得到你想要的...

回答by Faisal

Try using below Query:

尝试使用以下查询：

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

回答by Sanjit Rijal

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