从多个表、组和计算百分比中选择计数

ibrsph3r 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(387)

目前我有一个查询，它减去通过 mv_donation_report 表中，根据 mv_fundraiser_report 如图所示：

SELECT cast(A.NUM as float)/cast(A.DENOM as float) * 100 AS Percentage
FROM
(SELECT
(SELECT COUNT(*) 
FROM "public"."mv_donation_report"
WHERE "public"."mv_donation_report"."opt_in" = TRUE
[[AND "public"."mv_donation_report"."page_name" = {{NonProfit}}]]
[[AND {{DateRangeDM}}]]) AS NUM,

(SELECT COUNT(*) 
FROM "public"."mv_fundraiser_report" 
WHERE "public"."mv_fundraiser_report"."thank_you_delivered" = TRUE
[[AND "public"."mv_fundraiser_report"."ngo_name" = {{NonProfit}}]]
[[AND {{DateRange}}]]) AS DENOM) A

现在我返回一个百分比，但我想删除 WHERE ngo_name 参数，并返回按分组的多个百分比 ngo_name / page_name （两者具有相同的含义和功能）。
这就是数据输出的样子：

ngo_name|percentage
ngo1.   |10
ngo2.   |21
ngo3.   |35
ngo4.   |7

这可能吗？感谢您的帮助，谢谢！

sql postgresql Join group-by count

来源：https://stackoverflow.com/questions/62175056/select-count-from-multiple-tables-group-and-calcualte-percentage

1条答案

按热度按时间

2hh7jdfx1#

你在找我吗 join 两个按名称聚合的查询，像这样吗？

select dr.name, 1.0 * dr.cnt / fr.cnt as percentage
from ( 
    select page_name as name, count(*) as cnt
    FROM public.mv_donation_report
    where opt_in and {{DateRangeDM}}
    group by page_name
) dr
inner join (
    select ngo_name as name, count(*) as cnt
    FROMpublic.mv_fundraiser_report 
    where thank_you_delivered and {{DateRangeDM}}
    group by ngo_name
) fr on fr.name = dr.name

赞(0）回复(0）举报 2021-07-26

我来回答

从多个表、组和计算百分比中选择计数

1条答案

相关问题

热门标签

最新问答