2

I have two tables and joining them on customer_id.

The first table is deal and I store the data of a deal there. And every deal has volume and rest , pay etc.

The second table is handle and it's hard for me explain what is purpose of this table but the handle table is the same deal table and it has volume_handle, rest_handle, pay_handle etc.

I have to use the left join because i want all records in deal table and the matched records from handle table

I want to sum volume and sum rest from deal and sum volume_handle from handle and the relationship between these tables is customer_id and buy_id.

for example the deal table:

id = 1
volume = 1000
rest = 1000
customer_id = 1
---------------
id = 2
volume = 500
rest = 0
customer_id = 1
---------------
id = 3 
volume = 2000
rest = 0
customer_id = 2

and handle table is : 

id = 1
volume_handle = 3000
buy_id = 1

the query i write is :

select sum(deal.rest) as rest , sum(deal.volume) as volume , sum(handle.volume_handle) as handle  
from deal 
left join handle on deal.customer_id = handle.buy_id  
group by deal.customer_id;

and the result of this query is :

//when customer_id is 1
volume = 1500
rest = 1000
handle = 6000

//when customer_id is 2
volume = 2000
rest = 0
handle = null

the volume and the rest is right but the handle from second table is wrong because the result of sum(handle.volume_handle) is 3000 not 6000(when customer_id is 1 )

and i don't know how use aggregate functions before joining the tables.

anyone here can write the query for this problem?

Nick
  • 138,499
  • 22
  • 57
  • 95
sajadsholi
  • 173
  • 1
  • 3
  • 12

1 Answers1

2

Because you can have multiple rows in handle for each deal.customer_id value, you need to perform aggregation in that table before you JOIN it to deal. Something like this:

SELECT d.customer_id,
       SUM(d.rest) AS rest,
       SUM(d.volume) AS volume,
       MAX(h.volume_handle) AS handle
FROM deal d
LEFT JOIN (SELECT buy_id, SUM(volume_handle) AS volume_handle
           FROM handle
           GROUP BY buy_id) h ON h.buy_id = d.customer_id
GROUP BY d.customer_id

Output:

customer_id rest    volume  handle
1           1000    1500    3000
2           0       2000    null

Demo on dbfiddle

Note that I have used MAX around h.volume_handle, this won't change the result (as all the values it will test will be the same) but will be required to avoid any only_full_group_by errors.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • when i replace MAX to SUM the output be wrong again and give me 6000 not 3000. i want sum of handle_volume not max of it :) – sajadsholi Apr 20 '19 at 08:32
  • oh now i got that . you use the sum function in subquery and get that with max . thanks it works – sajadsholi Apr 20 '19 at 08:35
  • @sajadsholi yes, the `MAX` is *only* to avoid `only_full_group_by` errors. The `SUM` is performed in the subquery, and the `MAX` just ensures we only get one result. You could just as easily use `MIN`. – Nick Apr 20 '19 at 08:37