求助：统计CURRENTLY ENROLLED表各类别人数及占比的SQL查询

方法一：单查询实现汇总统计与占比计算 #

可以通过CASE表达式结合基础聚合函数，一次性完成各类别数量、占比和总人数的统计，无需多次执行查询：

SELECT
  COUNT(CASE WHEN `status` = 'Supporter' THEN 1 END) AS SUPPORTERS,
  ROUND(COUNT(CASE WHEN `status` = 'Supporter' THEN 1 END) * 100.0 / COUNT(*), 2) AS SUPPORTERS_PERCENT,
  COUNT(CASE WHEN `value` = 'Opposition' THEN 1 END) AS OPPOSITION,
  ROUND(COUNT(CASE WHEN `value` = 'Opposition' THEN 1 END) * 100.0 / COUNT(*), 2) AS OPPOSITION_PERCENT,
  COUNT(CASE WHEN `value` = 'Undecided' THEN 1 END) AS UNDECIDED,
  ROUND(COUNT(CASE WHEN `value` = 'Undecided' THEN 1 END) * 100.0 / COUNT(*), 2) AS UNDECIDED_PERCENT,
  COUNT(*) AS TOTAL
FROM `CURRENTLY ENROLLED`;

说明：

• COUNT(*)自动计算总人数，无需手动输入固定值，避免数据更新后数值脱节
• 乘以100.0是为了规避整数除法问题（比如13/29若用整数计算会得到0，转成浮点数才能得到正确的小数结果）
• ROUND(..., 2)用于将百分比保留两位小数，可根据需求调整保留位数

方法二：按类别行展示结果（分组视图） #

如果希望每个类别单独占一行展示，更清晰直观，可使用UNION ALL结合子查询：

WITH total AS (
  SELECT COUNT(*) AS total_count FROM `CURRENTLY ENROLLED`
)
SELECT
  'Supporter' AS CATEGORY,
  COUNT(*) AS COUNT,
  ROUND(COUNT(*) * 100.0 / (SELECT total_count FROM total), 2) AS PERCENTAGE
FROM `CURRENTLY ENROLLED`
WHERE `status` = 'Supporter'
UNION ALL
SELECT
  'Opposition' AS CATEGORY,
  COUNT(*) AS COUNT,
  ROUND(COUNT(*) * 100.0 / (SELECT total_count FROM total), 2) AS PERCENTAGE
FROM `CURRENTLY ENROLLED`
WHERE `value` = 'Opposition'
UNION ALL
SELECT
  'Undecided' AS CATEGORY,
  COUNT(*) AS COUNT,
  ROUND(COUNT(*) * 100.0 / (SELECT total_count FROM total), 2) AS PERCENTAGE
FROM `CURRENTLY ENROLLED`
WHERE `value` = 'Undecided';

重要提示 #

你之前的查询中，支持者用status字段，反对者和未决者用value字段，这大概率是笔误。请务必确认表结构，确保统计字段统一（比如是否都是status或value），否则会导致统计结果失真。

内容的提问来源于stack exchange，提问作者Greeneel