mysql - 在 SQL 查询中获取几个不同的计数

Question

我正在做这个查询

SELECT zip, COUNT(*) AS amount 
FROM table
WHERE group_id = 19
GROUP BY zip ORDER BY amount DESC

这为我提供了一个列表，其中包含每个邮政编码中 group_id 19 的出现次数。但我还想要每个 zip 的所有 groups_id 的总数(没有 WHERE 子句)。像这样(伪代码)

SELECT zip, COUNT(*) AS amount, COUNT(*) FOR THIS ZIP AS total, (amount/total * 100) AS percent
FROM table
WHERE group_id = 19
GROUP BY zip ORDER BY amount DESC 

这可能吗？

Answer 1

您可以使用条件聚合:

SELECT zip, COUNT(*) as amount, SUM(group_id = 19) AS grp19_amount,
       AVG(group_id = 19) as percent
FROM table
GROUP BY zip
ORDER BY grp19_amount DESC