mysql query on negative value

I am using following sql query...

 SELECT COALESCE(s.open_bal,0)
        AS open_balance
      , COALESCE(sum(i.amount),0)
        AS gross_fee
      , COALESCE(sum(i.discount),0)
        AS discount 
      , COALESCE(sum(i.amount) - sum(i.discount),0)
        AS net_payable
      , COALESCE(SUM(r.reg_fee+r.tut_fee+r.other_fee),0)
        AS net_recieved
      , COALESCE(sum(i.amount), 0)
          - COALESCE(sum(开发者_如何学运维i.discount), 0)
          - COALESCE(SUM(r.reg_fee+r.tut_fee+r.other_fee), 0)
        AS balance_due
      , b.name
        AS batch
      , b.id 
        AS batch_id
 FROM batches

Then just add IF() for a negative balance:

COALESCE(sum(i.amount) - sum(i.discount), 0) 
+ IF(s.open_bal < 0, -s.open_bal, 0) 
AS net_payable

edit: Moving on to fix the query.

going down from subscribers to both invoices and receipts is wrong idea itself. You're joining receipts to invoices, and if there are more then 1 of any, the other side of join will be summed twice (N times, actually).

Now, I'll give a basic idea how the query should look. I'm not going into COALESCE() thing - I believe it's unnecessary, as SUM() shouldn't return NULLs.

select
    b.name as batch_name,
    b.id as batch_id,
    s.open_bal as open_balance,
    invoices_sum.amount_sum as gross_fee,
    invoices_sum.discount_sum as discount,
    invoices_sum.amount_sum - invoices_sum.discount_sum
    + if(s.open_bal < 0, -s.open_bal, 0) as net_payable,
    recipts_sum.fee_sum as net_recieved
from fm_batches b
left join subscribers as s
    on batch = b.id
left join (
  select student_id, sum(amount) as amount_sum, sum(discount) as discount_sum
  from invoices
  group by student_id
) as invoices_sum
    on s.id = invoices_sum.student_id
left join (
  select student_id, sum(reg_fee + tut_fee + other_fee) as fee_sum
  from recipts
  group by student_id
) as recipts_sum
    on s.id = recipts_sum.student_id

You get the idea.

(COALESCE(sum(i.amount) - sum(i.discount),0) - COALESCE(s.open_bal,0)) AS net_payable

First, you can rewrite your query as:

 SELECT open_balance
      , gross_fee
      , discount 
      , ( gross_fee - discount )
          AS net_payable
      , net_recieved
      , ( gross_fee - discount - net_recieved )
          AS balance_due
      , batch
      , batch_id
 FROM
 ( SELECT COALESCE(s.open_bal,0)
            AS open_balance
        , COALESCE(sum(i.amount),0)
            AS gross_fee
        , COALESCE(sum(i.discount),0)
            AS discount 
        , COALESCE(SUM(r.reg_fee+r.tut_fee+r.other_fee),0)
            AS net_recieved
        , b.name
            AS batch
        , b.id 
            AS batch_id
   FROM fm_batches b 
       LEFT JOIN subscribers s
           ON s.batch = b.id
       LEFT JOIN invoices i 
           ON i.student_id = s.id 
       LEFT JOIN recipts r 
           ON r.student_id = s.id 
           AND r.status = "confirmed"
   GROUP BY b.name
 ) AS grp

Then, change:

  , ( gross_fee - discount )
      AS net_payable

into:

  , ( gross_fee - discount )
      + IF( open_balance >= 0 , 0 , open_balance )
      AS net_payable