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

loading...


0

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 = 1is_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回答

  • 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


  • 1

    如果使用 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;
    

loading...

评论

暂时没有评论!