How do I sum amount for specific user in mysql query?

user_17000366 0
Viewed 38

I have three tables I am working with:

  1. users table contains users id, name and status
  2. supply_history table contains id, users_id, amount and status
  3. rejected_deal table contains id, users_id, rejected_amount and also status.

What I am trying to find is to sum up each users supply_amount and rejected amount where users status, supply_history status and rejected_deal status is 1.

Here is what I have tried so far.

SELECT users.id, users.name, 
sum(supply_history.amount) as supply_amount, 
sum(rejected_deal.rejected_amount) as rejected_amount
from users 
inner join supply_history on supply_history.users_id=users.id 
inner join rejected_deal  on rejected_deal.users_id=users.id 

where users.status='1'  and rejected_deal.status='1' and supply_history.status='1'
GROUP by users.id;

My current query result is bellow.

id name supply_amount rejected_amount
1 Skipper 50 20
2 Private 200 20

The result I am looking for

id name supply_amount rejected_amount
1 Skipper 50 10
2 Private 100 20

Bellow is my three tables

  1. users table.
id name status
1 Skipper 1
2 Private 1
  1. supply_history table.
id users_id amount status
1 1 20 1
2 1 30 1
3 2 100 1
  1. rejected_deal table.
id users_id rejected_amount status
1 2 15 1
2 2 5 1
3 1 10 1

How do I solve this query please help. Thank you.

join mysql sql
3 Answers

You are joining supply_history rows and rejected_deal rows to user rows. But supply_history and rejected_deal are not directly related. For one user, you create thus all combinations of supply_history and rejected_deal. The result is:

id name status sh_id users_id amount status rd_id users_id rejected_amount status
1 Skipper 1 1 1 20 1 2 2 5 1
1 Skipper 1 2 1 30 1 3 1 10 1
2 Private 1 3 2 100 1 1 2 15 1
2 Private 1 3 2 100 1 1 2 15 1

When you aggregate this data set, you add up amounts multifold. (Each value gets multiplied with the number of rows for the user in the other table).

Instead join the sums to the user:

select u.id, u.name, sh.supply_amount, rd.rejected_amount
from users u
left outer join 
(
  select users_id, sum(amount) as supply_amount
  from supply_history
  where status = 1
  group by users_id
) sh on sh.users_id = u.id
left outer join
(
  select users_id, sum(rejected_amount) as rejected_amount
  from rejected_deal  
  where status = 1
  group by users_id
) rd on rd.users_id = u.id 
where u.status = 1
order by u.id;

I've turned your inner joins into outer joins for the case a user doesn't have entries in both tables. If you only want users that have data in both tables, then change this back to inner joins.

And if you want to enhance the query's readability, you may consider using WITH clauses: https://dev.mysql.com/doc/refman/8.0/en/with.html.

user_22707620

use Over for sum like this

SELECT users.id, users.name, 
sum(supply_history.amount) OVER(PARTITION BY users.id, ORDER BY users.id)  as supply_amount, 
sum(rejected_deal.rejected_amount) OVER(PARTITION BY users.id, ORDER BY users.id) as rejected_amount
from users 
inner join supply_history on supply_history.users_id=users.id 
inner join rejected_deal  on rejected_deal.users_id=users.id 
where users.status='1'  and rejected_deal.status='1' and supply_history.status='1'
user_23496830

try using sub queries to be something like that :

SELECT users.id, users.name, 
(select sum(amount)  from supply_history s where s.users_id = users.id and s.status = '1' ) as supply_amount ,
(select sum(rejected_amount)  from rejected_deal r where r.users_id = users.id and r.status = '1' ) as rejected_amount
from users
where users.status='1';
user_112365630
Related