chancj chancj - 4 years ago 152
MySQL Question

mysql calculation on cross tables

I've three tables in mysql with data as below, and I would like to know how do I get the following output. I don't know what's wrong with my coding?

select
rev.memberid,
(sum(rev.earned)/rule.revperpoint) - sum(redeem.redeempoint) as bal
from rev
left join rule on rev.rulename = rule.rulename
inner join redeem on rev.memberid = redeem.memberid
group by rev.memberid;


table1 rule

rulename revperpoint
CNY 2
NY 1


table2 rev

memberid earned rulename
37638899 500.50 CNY
37638899 400.50 CNY
25264833 300.50 CNY
2526833 600.50 CNY


table3 redeem

memberid redeempoint
25264833 100.00
25264833 50.00


expected output

memberid bal
25264833 300.50
37638899 450.50

Answer Source

Based on your desired result, the 4th line in table rev should be

25264833   600.50  CNY

And here you should not use inner join, cause it will only retrive record which memberid is 25264833, try this:

select 
    rev.memberid,
    (sum(rev.earned) / rule.revperpoint ) - coalesce(redeem.redeempoint, 0) as bal
from rev
left join rule on rev.rulename = rule.rulename
left join (select memberid, sum(redeem.redeempoint) as redeempoint from redeem group by memberid) redeem on rev.memberid = redeem.memberid
group by rev.memberid;

And demo here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download