MySQL 获取“Count(*)”占“GROUP BY”中所有项目数的百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3061655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting percentage of "Count(*)" to the number of all items in "GROUP BY"
提问by pars
Let's say I need to have the ratioof "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this:
比方说，我需要有比例的，以“所有项目的数量”，“从某些类别的商品数量”。请考虑这样的 MySQL 表：
/*
mysql> select * from Item;
+----+------------+----------+
| ID | Department | Category |
+----+------------+----------+
| 1 | Popular | Rock |
| 2 | Classical | Opera |
| 3 | Popular | Jazz |
| 4 | Classical | Dance |
| 5 | Classical | General |
| 6 | Classical | Vocal |
| 7 | Popular | Blues |
| 8 | Popular | Jazz |
| 9 | Popular | Country |
| 10 | Popular | New Age |
| 11 | Popular | New Age |
| 12 | Classical | General |
| 13 | Classical | Dance |
| 14 | Classical | Opera |
| 15 | Popular | Blues |
| 16 | Popular | Blues |
+----+------------+----------+
16 rows in set (0.03 sec)
mysql> SELECT Category, COUNT(*) AS Total
-> FROM Item
-> WHERE Department='Popular'
-> GROUP BY Category;
+----------+-------+
| Category | Total |
+----------+-------+
| Blues | 3 |
| Country | 1 |
| Jazz | 2 |
| New Age | 2 |
| Rock | 1 |
+----------+-------+
5 rows in set (0.02 sec)
*/
What I need is basically a result set resembles this one:
我需要的基本上是一个类似于这个的结果集：
/*
+----------+-------+-----------------------------+
| Category | Total | percentage to the all items | (Note that number of all available items is "9")
+----------+-------+-----------------------------+
| Blues | 3 | 33 | (3/9)*100
| Country | 1 | 11 | (1/9)*100
| Jazz | 2 | 22 | (2/9)*100
| New Age | 2 | 22 | (2/9)*100
| Rock | 1 | 11 | (1/9)*100
+----------+-------+-----------------------------+
5 rows in set (0.02 sec)
*/
How can I achieve such a result set in a single query?
如何在单个查询中实现这样的结果集？
Thanks in advance.
提前致谢。
回答by bleeeah
SELECT Category, COUNT(*) AS Total , (COUNT(*) / (SELECT COUNT(*) FROM Item WHERE Department='Popular')) * 100 AS 'Percentage to all items',
FROM Item
WHERE Department='Popular'
GROUP BY Category;
I'm not sure of the MySql syntax, but you can use a sub-query as shown.
我不确定 MySql 语法，但您可以使用如图所示的子查询。
回答by the_void
This should do it:
这应该这样做：
SELECT I.category AS category, COUNT(*) AS items, COUNT(*) / T.total * 100 AS percent
FROM Item as I,
(SELECT COUNT(*) AS total FROM Item WHERE Department='Popular') AS T
WHERE Department='Popular'
GROUP BY category;
回答by Despertar
SET @total=0;
SELECT Category, count(*) as Count, count(*) / @total * 100 AS Percent FROM (
SELECT Category, @total := @total + 1
FROM Item
WHERE Department='Popular') temp
GROUP BY Category;
An advantage of doing it this way is you do not have to duplicate the WHEREcondition, which is a ticking time bomb the next time someone comes by to update the condition, but doesn't realize it's in two different places.
这样做的一个优点是您不必复制WHERE条件，这是下次有人来更新条件时的定时炸弹，但没有意识到它位于两个不同的地方。
Avoiding the duplicate WHEREcondition also improves readability, especiallyif your WHEREis more complex (with multiple joins, etc).
避免重复WHERE条件还可以提高可读性，特别是如果您的WHERE条件更复杂（具有多个连接等）。
