mysql - 使用 MySQL 的列组的百分比总计

Question

如果我有一个包含以下内容的表格:

+---------+-------------+
| Country | campaign_id |
+---------+-------------+
| US      |           1 |
| FR      |           1 |
| FR      |           1 |
| UK      |           1 |
| NL      |           2 |
| DE      |           2 |
+---------+-------------+

如何像这样返回每个国家/地区的百分比:

+---------+------------+
| Country | Percentage |
+---------+------------+
| US      | 25         |
| FR      | 50         |
| UK      | 25         |
+---------+------------+

这是我目前为止所做的:

SELECT country FROM campaigns WHERE campaign_id = 1

请注意，如果更容易，返回 0.25 也是可以的。

Answer 1

编写一个子查询以获取事件中的总行数，然后将其除以按国家/地区分组的行数。

SELECT a.country, ROUND(100 * COUNT(*)/b.total, 2) AS percentage
FROM yourTable AS a
CROSS JOIN (
  SELECT COUNT(*) AS total
  FROM yourTable
  WHERE campaign_id = 1) AS b
WHERE a.campaign_id = 1
GROUP BY a.country

DEMO