Postgresql - Conditional SUM with correlated subquery

Question

I want to calculate the SUM of a CASE condition where one of the conditions uses a correlated subquery to find the results but i'm stuck with the syntax.

Here is a sample of the query that i created until now but with errors:

Updated

SELECT t1.account,
       t1.date
       SUM(CASE WHEN condition1 THEN t1.amount
                WHEN condition2 THEN (select sum(t2.amount) from t2 where t2.account = t1.account and t2.date = t1.date)
                ELSE 0 END) as total
FROM t1
GROUP BY t1.account, t1.date

The above query returns wrong results when the output of the subquery are multiple records. In a nutshell the result of condition2 is sum(t2.amount) * (number of records).

I tried removing the sum() function from the subquery but since it can fetch multiple records i still got an error.

I believe it can be done somehow but i'm stuck.

Can anyone suggest something.

Btw i'm using Postgresql 9.4

Gordon Linoff · Accepted Answer · 2017-07-18 00:02:14Z

2

You can do an aggregation first. As phrased, your query would seem to be trying to do this:

SELECT t1.account,
       SUM(CASE WHEN condition1 THEN t1.amount
                WHEN condition2 THEN t2.sumamount
                ELSE 0
           END) as total
FROM t1 LEFT JOIN
     (SELECT t2.account, SUM(t2.amount) as t2.sumamount
      FROM t2
      GROUP BY t2.account
     ) t2
     ON t2.account = t1.account
GROUP BY t1.account

answered Jul 18, 2017 at 0:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Giannis Dim Over a year ago

Thank you for the help. I have updated the query to be more precise to what i want and described the issue with the current behavior of the query. Even with your solution i have the same results. I believe this is due to the double aggregation, outer SUM(). Is there another way to do it avoiding this behaviour?

Clodoaldo Neto Over a year ago

@GiannisDim Since you are unable to express the expected output by writing a query you need to show data sample and the expected output as data.

Collectives™ on Stack Overflow

Postgresql - Conditional SUM with correlated subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related