我有一个包含以下各列的表：
Type | Date | Amount
当Type的值类似于'E'时，我使用SUM函数将值添加到“金额”列中（作为SUM1）。这是我的查询：
select
to_char("Date", 'Mon') as mon,
extract(year from "Date") as yyyy,
sum("Amount") as SUM1
from "Transactions"
where "Type" LIKE 'E%' and "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;
结果是这样的行：
mon | yyyy | SUM1
但是，我想做的是还要显示另一个总和列（SUM2），以显示“金额”中添加的值的总和，其中“类型”类似于“ P”，因此显示类似以下的行：
mon | yyyy | SUM1 | SUM2
取决于您的PostgreSQL版本。对于较老的，您可以使用以下条件求和CASE：
select to_char("Date", 'Mon') as mon,
extract(year from "Date") as yyyy,
sum(CASE WHEN "Type" LIKE 'E%' then "Amount" ELSE 0 END) as SUM1,
sum(CASE WHEN "Type" LIKE 'P%' then "Amount" ELSE 0 END) as SUM2
from "Transactions"
where "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;
或者，如果您使用的是PostgreSQL 9.4或更高版本，则可以使用更好的FILTER语法：
select to_char("Date", 'Mon') as mon,
extract(year from "Date") as yyyy,
sum("Amount") FILTER (WHERE "Type" LIKE 'E%') as SUM1,
sum("Amount") FILTER (WHERE "Type" LIKE 'P%') as SUM2
from "Transactions"
where "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句