mysql query on negative value -


i using following sql query...

 select coalesce(s.open_bal,0)         open_balance       , coalesce(sum(i.amount),0)         gross_fee       , coalesce(sum(i.discount),0)         discount        , coalesce(sum(i.amount) - sum(i.discount),0)         net_payable       , coalesce(sum(r.reg_fee+r.tut_fee+r.other_fee),0)         net_recieved       , coalesce(sum(i.amount), 0)           - coalesce(sum(i.discount), 0)           - coalesce(sum(r.reg_fee+r.tut_fee+r.other_fee), 0)         balance_due       , b.name         batch       , b.id          batch_id  batches 

then add if() negative balance:

coalesce(sum(i.amount) - sum(i.discount), 0)  + if(s.open_bal < 0, -s.open_bal, 0)  net_payable 

edit: moving on fix query.

going down subscribers both invoices , receipts wrong idea itself. you're joining receipts invoices, , if there more 1 of any, other side of join summed twice (n times, actually).

now, i'll give basic idea how query should look. i'm not going coalesce() thing - believe it's unnecessary, sum() shouldn't return nulls.

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

you idea.


Comments