I believe the issue in your code is in this statement here:
case when (c.netamt - d.paidamount) is not null then (c.netamt - d.paidamount) else 0
This will always be zero when either c.netamt or d.paidamount is zero. Null is not zero, so you cannot subtract from it or with it. What I would recommend, as a first step, would be as follows:
COALESCE(c.netamt, 0) - COALESCE(d.paidamount,0)
The final code would look like this:
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(COALESCE(c.netamt, 0) - COALESCE(d.paidamount,0)) 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
However, I'm not a fan of doing calculations inside a SUM statement. I would rather see you do a SELECT statement that got every record and did the calculations on it. Then make that a sub-query of a statement that did the SUMS on each column and grouped by the partnerid. That should make things clearer and more performant.
Also, I used the
COALESCE
statement instead of the CASE statement. COALESCE returns the first non-null value. It is cleaner than what you are doing and MySQL supports it. It should make your life easier.