跨3个连接的百分比计算

Question

我正在考虑计算一个百分比值以指示进度。我正在从同一列上的3个独立计数中加入数据（但值不同）。我是如此近，但它一直给我零。

SELECT a.count1 / (a.count1 + b.count2 + c.count3) FROM
(SELECT count(*) AS "count1"
FROM "stitch_jira"."issues"
WHERE "stitch_jira"."issues"."fields__status__statusCategory__name" = 'To Do') a
CROSS JOIN 
(SELECT count(*) AS "count2"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'In Progress')) b  
CROSS JOIN 
(SELECT count(*) AS "count3"
FROM "stitch_jira"."issues"
WHERE ("stitch_jira"."issues"."fields__status__statusCategory__name" = 'Done')) c

关于为什么我在这里获得零结果的任何建议？它应该产生" 0.4 ..."

Answer 1

您可以使用dentitinal聚合来重写整个查询，然后铸造为十进制/乘以1.0：

SELECT 
 1.0*(COUNT(*) FILTER(WHERE fields__status__statusCategory__name='To Do'))/
 (COUNT(*) FILTER(WHERE fields__status__statusCategory__name 
                   IN('To Do','In Progress','Done')))
FROM "stitch_jira"."issues"