提问人：Koala 提问时间：3/17/2022 最后编辑：Koala 更新时间：3/17/2022 访问量：93
按 SQL 表分组划分
Division in group by SQL table
问：
我有以下SQL代码
SELECT (COUNT(*) filter (WHERE has_a)) AS count_a,
COUNT(*) AS total_count,
process_date
FROM(SELECT process_date::date AS process_date,
(CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a
FROM my_table)
temptable
GROUP BY process_date
LIMIT 5;
给出下表
我想创建一个名为 percent_a 的列，其值按 分组。例如，对于第 1 行，新列的值为 49.4，即 （1030/2085）*100。(count_a/total_count)*100process_date
我试过了
SELECT process_date,
((COUNT(*) filter (WHERE has_a))/COUNT(*) * 100) AS percent_a,
FROM(SELECT process_date::date AS process_date,
(CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a,
FROM my_table)
temptable
GROUP BY process_date
ORDER BY process_date DESC
LIMIT 1;
但这只是给了 0。
如何创建列以显示我想要的百分比？我认为发生了一些事情，但我不知道如何解决它。GROUP BY
答：
2赞
Andronicus
3/17/2022
#1
这是因为返回一个整数，你只需要强制转换它：count
SELECT process_date,
(((COUNT(*) filter (WHERE has_balance))::DOUBLE PRECISION)/COUNT(*) * 100) AS percent_a,
FROM(SELECT process_date::date AS process_date,
(CASE WHEN (columnA > 0) THEN true ELSE false END) AS has_a,
FROM my_table)
temptable
GROUP BY process_date
ORDER BY process_date DESC
LIMIT 1;
评论
select 1/2 x, 1.0/2 y