将“ Count（*）”占“ GROUP BY”中所有项目数的百分比
比方说，我需要有 的，以“所有项目的数量”，“从某些类别的商品数量”。请考虑这样的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)
*/
我需要的基本上是一个类似于该结果集的结果集：
/*+----------+-------+-----------------------------+
| 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)
*/
如何在 获得这样的结果集？
提前致谢。
回答：
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;
我不确定MySql的语法，但是可以使用如图所示的子查询。
以上是 将“ Count（*）”占“ GROUP BY”中所有项目数的百分比 的全部内容， 来源链接： utcz.com/qa/420975.html
