从SELECT GROUP BY Mysql获取百分比和总数

Question

经过多次搜索，我找不到适合我的解决方案。

我有下表：

id  category    entry_date
1   Suggestion  01/01/2019
2   Suggestion  05/01/2019
3   Compliment  05/01/2019
4   Complaint   12/02/2019
5   Suggestion  09/10/2019
6   Compliment  23/11/2019

我需要显示每个类别的数量和每个类别的百分比(基于总条目(。"地点"会限制日期范围，但我认为在这里这无关紧要。这是我的预期结果：

Category       Totals   % of Total Entries
Compliment     2        ˜34%
Complaint      1        16%
Suggestion     3        60%

这是我目前使用的查询：

SELECT category, 
COUNT(*) AS total, 
ROUND(COUNT(category)*100 / (SELECT COUNT(*))) AS pct 
FROM (Mytable) 
WHERE `entry_date` >= '2018-01-01' AND `entry_date` <= '2019-12-31' 
GROUP BY category ORDER BY category ASC

因此，pct是相对于每个类别的(所以：200100300(，而不是总数。

我使用的是MySQL 5.7。谢谢大家！

Answer 1

SELECT计数可能需要时间，因此在大表上不会很快

CREATE TABLE Table1
(`id` int, `category` varchar(10), `entry_date` varchar(10))
;

INSERT INTO Table1
(`id`, `category`, `entry_date`)
VALUES
(1, 'Suggestion', '01/01/2019'),
(2, 'Suggestion', '05/01/2019'),
(3, 'Compliment', '05/01/2019'),
(4, 'Complaint', '12/02/2019'),
(5, 'Suggestion', '09/10/2019'),
(6, 'Compliment', '23/11/2019')
;

SELECT `category`,     COUNT(*) AS cnt,
100.0 * COUNT(*) / (SELECT COUNT(*) FROM Table1) AS percent   FROM Table1 GROUP BY `category`

类别|cnt|percent：--------|--：|-------：建议|3|50.00000赞美|2|33.33333投诉|1 | 16.66667

db<gt；小提琴这里

Answer 2

您需要在MySQL 8.0中使用窗口函数：

with cte as (
select category, count(*) over (partition by category) as subtotal,
count(*) over () as total from following_table )
select category, subtotal, 
concat(round(subtotal*100/total), '%') as `% of Total Entries` 
from cte group by category;

输出：

+------------+----------+--------------------+
| category   | subtotal | % of Total Entries |
+------------+----------+--------------------+
| Complaint  |        1 | 17%                |
| Compliment |        2 | 33%                |
| Suggestion |        3 | 50%                |
+------------+----------+--------------------+

在MySQL 5.7中，使用两个查询即可完成此操作。第一个获得总数，第二个使用该结果计算每组的百分比。