Postgresql 9.1: I have a query that must return the values of a second table only if the aggregate function SUM of two columns is greater than zero.
This is the data:
Table a
id
---
1
2
3
Table b
id fk(table a)
---------------
1 1
2 null
3 3
Table c
id fk(table b) amount price
-----------------------------------
1 1 1 10 --positive
2 1 1 -10 --negative
3 3 2 5
As you can see, table b has some ids from table a, and table c can have 1 or more references to table b, table c is candidate to be retrieved only if the sum(amount * price ) > 0.
I wrote this query:
SELECT
a.id, b.id, SUM(c.amount * c.price) amount
FROM
tablea a
LEFT JOIN
tableb b ON b.fk = a.id
LEFT JOIN
tablec c ON c.fk = b.id
GROUP BY
a.id, b.id
HAVING
SUM(c.amount * c.price) > 0
But this query is not retrieving all rows from table a just the row 1 and I need the two rows. I understand this is happening because of the HAVING clause but I don't know how to rewrite it.
Expected result
a b sum
------------------
1 null null -- the sum of 1 * 10 (rows 1 and two) = 0 so its not retrieved.
2 null null -- no foreign key in second table
3 3 10 -- the sum of 2 * 5 (row 3) > 0 so it's ok.
Try this:
SELECT A.ID, B.ID, C.ResultSum
FROM TableA A
LEFT JOIN TableB B ON (B.FK = A.ID)
LEFT JOIN (
SELECT FK, SUM(Amount * Price) AS ResultSum
FROM TableC
GROUP BY FK
) C ON (C.FK = B.ID) AND (ResultSum > 0)
See demo here.