CODEWITHSUNDEEP
postgresqlaggregate-functionspsql
Sam
Sam

Reputation: 920

PSQL: Aggregate function (sum) not working

I have this query (artist_money is of MONEY type, e.g. $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

When I try to get the result, here's the error:

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 **********

What's going on? According to Documentation, SUM should take the parameters if it's MONEY type.

Thanks a lot!

Upvotes: 1

Views: 8626

Answers (2)

gmiley
gmiley

Reputation: 6604

You are trying to pass two parameters to an aggregate 1 parameter function.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270513

The problem is the comma, not the money type. Perhaps you intend:

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;

Or, if you want one column, then it makes much more sense to only use one 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;

Upvotes: 2

Related Questions