Select aspirasi_id
, aspirasi_option_id
, (Count(aspirasi_option_id)* 100 /
(Select Count(*)
From aspirasi_choices)
) as total
From aspirasi_choices
GROUP
BY aspirasi_option_id输出
错误的值
我怎么能发每张卡，他总各不在加入。
示例真值
谢谢你
表
my expectations
aspirasi_id | aspirasi_option_id | total
---------+----------+-------
52 | 6 | 100.00%
53 | 5 | 50.00%
53 | 4 | 50.00%
Reality
aspirasi_id | aspirasi_option_id | total
---------+----------+-------
52 | 6 | 33.33%
53 | 5 | 33.33%
53 | 4 | 33.33%
1条答案
按热度按时间ny6fqffe1#
我想你需要把百分比除以
aspirasi_id，在子查询中添加where子句，如WHERE aspirasi_id = a.aspirasi_id```SELECT
a.aspirasi_id,
a.aspirasi_option_id,
(
COUNT(aspirasi_option_id) * 100 /
(SELECT
COUNT(*)
FROM
aspirasi_choices
WHERE aspirasi_id = a.aspirasi_id)
) AS total
FROM
aspirasi_choices a
GROUP BY a.aspirasi_id,
a.aspirasi_option_id
SELECT
a.aspirasi_id,
a.aspirasi_option_id,
COUNT(aspirasi_option_id) * 100 / b.inner_total AS total
FROM
aspirasi_choices a
JOIN (SELECT
aspirasi_id,
COUNT(*) AS inner_total
FROM
aspirasi_choices
GROUP BY aspirasi_id) b USING (aspirasi_id)
GROUP BY a.aspirasi_id,
a.aspirasi_option_id