如何在group by子句之后应用case语句-Java 学习之路

INPUT:

我有一张 table ，看起来像：

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

PROBLEM STATEMENT:

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

EXPECTED OUTPUT

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

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

ATTEMPTED SOLUTION:

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

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;

ERROR:

我收到以下错误：

列“rev.is_realised”必须出现在GROUP BY子句中或用于聚合函数LINE 2：CASE WHEN is_realised = 1

2 回答

在 sum 函数中移动case语句：

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

回复于 2024-04-26T05:44:36+08:00

如果使用 PG9.4+ ，请使用效率更高的 FILTER 子句：

SELECT "date",
       sum(mrp) FILTER (WHERE is_shipped = 1) AS shipped_mrp,
       sum(mrp) FILTER (WHERE is_realised = 1) AS realised_mrp,
       sum(mrp) AS mrp
FROM rev
GrOUP BY 1;

回复于 2024-04-26T05:44:36+08:00

如何在group by子句之后应用case语句

2 回答

相关问题