I am using PostgreSQL as my database and I need to write the query that returns:
- a
user_id
from 1st table - sum of every row's
amount
from 2nd table (whereuser_id
is the same as in the 1st table) - sum of every row's
amount
from 3nd table (whereuser_id
is the same as in the 1st table and the type is defined).
So I wrote this query with two LEFT JOINs, but it doesn't work as expected:
SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet, COALESCE(SUM(d.amount), 0) as total_deposit
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE aff.code = 'code'
GROUP BY aff.user_id;
This query returns the wrong numbers for total_bet
and total_deposit
columns.
If I omit the second LEFT JOIN, like this:
SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
WHERE aff.code = 'code'
GROUP BY aff.user_id;
everything works fine.
What can be the problem and how can I solve it?
Best Answer
Let's assume your tables are defined:
... and we populate them with some sample data:
In order to understand why your query is not working as you expect, check first the result of executing your same query, but without
GROUP BY
, and changing your aggregates by just values (I've also added some extra ids for ease of interpretation):This is what you'll get:
As there are not any relationship between your tables
t
andd
, what you actually get is thecartesian product
of both of them. You clearly see it because you have all possible combinations oftransfer_id
anddeposit_id
.You need to make this by using
subquerys
, instead:That will get you what you expect:
See Identifying and Eliminating the Dreaded Cartesian Product