To compute sum regarding to a constraint

Question

I'm using PostgreSQL 8.4.

I have the following sql-query:

SELECT p.partner_id,
CASE WHEN pa.currency_id = 1 THEN SUM(amount) ELSE 0 END AS curUsdAmount,
CASE WHEN pa.currency_id = 2 THEN SUM(amount) ELSE 0 END AS curRubAmount,
CASE WHEN pa.currency_id = 3 THEN SUM(amount) ELSE 0 END AS curUahAmount
FROM public.player_account AS pa
JOIN player AS p ON p.id = pa.player_id
WHERE p.partner_id IN (819)
GROUP BY p.partner_id, pa.currency_id

The thing is that query does not what I expected. I realize that, but now I want to understand what exactly that query does. I mean, what SUM will be counted after the query executed. Could you clarify?

You probably want `SUM(CASE WHEN pa.currency_id = 1 THEN amount ELSE 0 END) AS curUsdAmount` — juergen d, Nov 24 '14 at 17:00
@juergend Yeah, absolutely, but what sum's computed with my sql-script. — St.Antario, Nov 25 '14 at 07:53

score 2 · Accepted Answer · edited Nov 24 '14 at 17:26

2

I think you have the conditions backwards in the query:

SELECT p.partner_id,
       SUM(CASE WHEN pa.currency_id = 1 THEN amount ELSE 0 END) AS curUsdAmount,
       SUM(CASE WHEN pa.currency_id = 2 THEN amount ELSE 0 END) AS curRubAmount,
       SUM(CASE WHEN pa.currency_id = 3 THEN amount ELSE 0 END) AS curUahAmount
FROM public.player_account pa JOIN
     player p
     ON p.id = pa.player_id
WHERE p.partner_id IN (819)
GROUP BY p.partner_id;

Note that I also removed currency_id from the group by clause.

edited Nov 24 '14 at 17:26

GarethD

68,045
10
83
123

answered Nov 24 '14 at 17:01

Gordon Linoff

1,242,037
58
646
786

Yeah, exactly that's what I was looking for. But couldn't you explain what sum I compute in my script? I think I compute the sum over all stores and replace it with 0 if... – St.Antario Nov 25 '14 at 07:56

score 0 · Answer 2 · edited May 23 '17 at 11:57

Maybe one row per (partner_id, currency_id) does the job. Faster and cleaner that way:

SELECT p.partner_id, pa.currency_id, sum(amount) AS sum_amount
FROM   player_account pa
JOIN   player p ON p.id = pa.player_id
WHERE  p.partner_id = 819
AND    pa.currency_id IN (1,2,3)  -- may be redundant if there are not other
GROUP  BY 1, 2;

If you need 1 row per partner_id, you are actually looking for "cross-tabulation" or a "pivot table". In Postgres use crosstab() from the additional module tablefunc , which is very fast. (Also available for the outdated version 8.4):

SELECT * FROM crosstab(
   'SELECT p.partner_id, pa.currency_id, sum(amount)
    FROM   player_account pa
    JOIN   player p ON p.id = pa.player_id
    WHERE  p.partner_id = 819
    AND    pa.currency_id IN (1,2,3)
    GROUP  BY 1, 2
    ORDER  BY 1, 2'

   ,VALUES (1), (2), (3)'
   ) AS t (partner_id int, "curUsdAmount" numeric
                         , "curRubAmount" numeric
                         , "curUahAmount" numeric); -- guessing data types

Adapt to your actual data types.

Detailed explanation:

PostgreSQL Crosstab Query

To compute sum regarding to a constraint

2 Answers2