Subtract two values ​​from two tables when zero values ​​exist

Question

select p.partnerid, sum(case when c.amount is not null then c.amount else 0 end) as amount, sum(case when c.netamt is not null then c.netamt else 0 end) as total, sum(case when (c.netamt - d.paidamount) is not null then (c.netamt - d.paidamount) else 0 end) as remainingamount, sum(case when d.paidamount is not null then d.paidamount else 0 end) as paidamt from customerinfo c left join dailypayments d on c.accno = d.accno right join partnerinfo p on c.partnerid = p.partnerid where (d.paiddate is null or (d.paiddate >= '2011-3-15' and d.paiddate <= '2012-6-13')) and p.manager = 7 group by p.partnerid 

From the above query, I need to subtract two values ​​from two tables for which there is no value in the second table.

for a better understanding see image below.

Answer 1

Use IFNULL() to remove the case and simplify the calculation:

 sum(c.netamt - ifnull(d.paidamount, 0)) as remainingamount,