postgresql - PSQL:聚合函数(总和)不起作用

Question

我有这个查询(artist_money 是 MONEY 类型，例如 $30,456.11。):

SELECT SUM(
CASE
WHEN end_date - date '2015-12-3' <= 28 AND end_date - date '2015-12-3' > 0 THEN artist_money
END,
CASE
WHEN date '2015-12-3' - start_date > 28 THEN artist_money
END
) AS "gonorar"
FROM peacecard 
WHERE artist_id = 12345 AND contract IS NOT NULL

当我尝试获取结果时，出现以下错误:

ERROR:  function sum(money, money) does not exist
LINE 1: SELECT sum(
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

这是怎么回事？根据 Documentation ，如果 SUM 是 MONEY 类型，则应该采用参数。

非常感谢!

Answer 1

问题是逗号，而不是货币类型。也许您打算:

SELECT SUM(CASE WHEN end_date - date '2015-12-3' <= 28 AND end_date - date '2015-12-03' > 0
                THEN artist_money
           END),
       SUM(CASE WHEN date '2015-12-3' - start_date > 28
                THEN artist_money
           END
          ) AS "gonorar"
FROM peacecard 
WHERE artist_id = 12345 AND contract IS NOT NULL;

或者，如果您想要一列，那么只使用一个 case 更有意义:

SELECT SUM(CASE WHEN end_date - date '2015-12-3' <= 28 AND end_date - date '2015-12-03' > 0
                THEN artist_money
                WHEN date '2015-12-3' - start_date > 28
                THEN artist_money
           END
          ) AS "gonorar"
FROM peacecard 
WHERE artist_id = 12345 AND contract IS NOT NULL;