CODEWITHSUNDEEP
mysqllaravel
Fasil kk
Fasil kk

Reputation: 2187

SUM of the field multiplies when the main table join with another manyToOne relationship table

select sum(t.amount) user_amount_sum 
  from users u
  join investor_transactions t
    on u.id = t.investor_id
 group 
    by u.id;

returns user_amount_sum value 40 for each users. (correct)

select sum(investor_transactions.amount) AS user_amount_sum from users inner join merchant_user on merchant_user.user_id = users.id inner join investor_transactions on users.id = investor_transactions.investor_id group by users.id;

But now returns user_amount_sum value 200 for each users. (5 times more, there are 5 rows in merchant_user table for each user)

NB 1: The only difference between both query is 

inner join `merchant_user` on `merchant_user`.`user_id` = `users`.`id

NB 2: I'm joining merchant_user table for filtering purpose.

What is the best practice to solve this issue.?

Upvotes: 1

Views: 32

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

SUM, and all aggregate functions, operate on the intermediate result (of the FROM...WHERE); you will encounter this issue any time multiple 1:N relationships are joined. Since you're only using the additional join for filtering, you should be able to use alternatives like:

SELECT SUM(investor_transactions.amount) AS user_amount_sum 
FROM users 
INNER JOIN investor_transactions ON users.id = investor_transactions.investor_id 
INNER JOIN (SELECT DISTINCT user_id FROM merchant_user) AS mu ON mu.user_id = users.id 
GROUP BY users.id;

or 

SELECT SUM(investor_transactions.amount) AS user_amount_sum 
FROM users 
INNER JOIN investor_transactions ON users.id = investor_transactions.investor_id 
WHERE users.id IN (SELECT DISTINCT user_id FROM merchant_user) 
GROUP BY users.id;

or 

SELECT SUM(investor_transactions.amount) AS user_amount_sum 
FROM users AS u
INNER JOIN investor_transactions ON u.id = investor_transactions.investor_id 
WHERE EXISTS (SELECT * FROM merchant_user AS mu WHERE mu.user_id = u.id) 
GROUP BY users.id;

Edit: though now that I look at it, you can probably skip the users table since you're only using the id field from it, like this...

SELECT SUM(t.amount) AS user_amount_sum 
FROM investor_transactions AS t
INNER JOIN (SELECT DISTINCT user_id FROM merchant_user) AS mu ON t.user_id = mu.user_id
GROUP BY t.user_id;

and similar for the other alternatives above.

Upvotes: 1

Related Questions