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.

CodePudding user response：

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.

CodePudding user response：

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'

CodePudding user response：

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';