postgresql - 如何在 group by 子句后应用 case when 语句

Question

输入:

我有一张看起来像这样的表:

    date   | is_shipped | is_realised | mrp  
----------+------------+-------------+------
 12022015 |          1 |           1 | 1000
 12022015 |          0 |           1 | 2000
 12022015 |          1 |           0 | 3000
 13022015 |          1 |           1 | 1500
 13022015 |          0 |           1 | 2500
 13022015 |          1 |           0 | 3500

问题陈述:

我想按日期对行进行分组以获得总的 mrp 以及对应于 is_shipped = 1 和 is_realised = 1 的 mrp。例如，12022015 上的总 mrp 为 1000 + 2000 + 3000 = 6000。 12022015 上对应于 is_shipped = 1 的总 mrp 为 1000 + 3000 = 4000。在 12022015 上对应于 is_realised = 1 的总 mrp 为 1000 + 2000 = 3000。

预期输出

所以，我的结果应该是这样的:

    date   | shipped_mrp | realised_mrp | mrp  
----------+-------------+--------------+------
 12022015 |        4000 |         3000 | 6000
 13022015 |        5000 |         4000 | 7500

尝试的解决方案:

我尝试了以下查询，但它不起作用:

select sum(mrp),
CASE WHEN is_realised = 1
THEN 
    SUM(mrp)
ELSE
    0
END AS realised_mrp,
CASE WHEN is_shipped = 1
THEN 
    SUM(mrp)
ELSE
    0
END AS shipped_mrp
from rev
group by date;

错误:

我收到以下错误:

column "rev.is_realised" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: CASE WHEN is_realised = 1

Answer 1

将 case 语句移到 sum 函数中:

select 
  date, 
  sum(CASE WHEN is_shipped = 1 THEN mrp ELSE 0 END) AS shipped_mrp,
  sum(CASE WHEN is_realised = 1 THEN mrp ELSE 0 END) AS realised_mrp,
  sum(mrp) AS mrp
from rev
group by date;

Sample SQL Fiddle