获得"Count(*)"的百分比；到"GROUP BY"中所有项目的数量 [英] Getting percentage of "Count(*)" to the number of all items in "GROUP BY"

问题描述

比方说，我需要将特定类别中可用商品的数量"与所有商品的数量"的比值比率.请考虑这样的MySQL表:

Let's say I need to have the ratio of "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this:

/*

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?

提前谢谢.

推荐答案

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的语法，但是您可以使用如图所示的子查询.

I'm not sure of the MySql syntax, but you can use a sub-query as shown.

这篇关于获得"Count(*)"的百分比；到"GROUP BY"中所有项目的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！