Member Avatar for Member #1129692

hey, Previously I post my question. But it wasn't helped. So I tried own and make sum query.
It's not generate 0 value records. can anyone hep me ?

select
    loan.loan_id as 'Loan ID'
   ,loan.customer_name as 'Customer Name'
   ,loan.total_amount as 'Total Amount'
   ,ifnull(sum(settlement.amount),0) as 'Total Received'
   ,((loan.total_amount)-ifnull(sum(settlement.amount), 0))as 'Total Due'
from loan
inner join settlement  on  loan.loan_id =  settlement.loan_id
where loan.collector_name='kapoor'
group by loan.loan_id

By this code 2 records shows. But there is another loan collect by kapoor. And that loan still not collected ant payment. So I wanted to show Total Received = 0
Can help ?

  • 2 Contributors
  • 3 Replies
  • 296 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by hmondy

Dani AI

Generated

Short answer: the missing loan is being dropped by the join. Use an outer join so rows from the loan table with no matching settlement rows are kept, and convert the aggregated NULL to 0. is correct to point out NULL-to-zero handling, but the join type is the other root cause.

SELECT
  l.loan_id AS LoanID,
  l.customer_name AS CustomerName,
  l.total_amount AS TotalAmount,
  COALESCE(SUM(s.amount), 0) AS TotalReceived,
  l.total_amount - COALESCE(SUM(s.amount), 0) AS TotalDue
FROM loan AS l
LEFT JOIN settlement AS s
  ON l.loan_id = s.loan_id
WHERE l.collector_name = :collector_name   -- replace :collector_name with your collector value
GROUP BY l.loan_id, l.customer_name, l.total_amount;

Troubleshooting checklist if the loan still does not appear:

  • Verify the loan row actually has collector_name matching the WHERE value (watch for trailing spaces or different casing).
  • Confirm settlement.loan_id values match loan.loan_id (same type/format).
  • Run a simple existence test for that loan id in settlement to confirm zero payments.
  • If MySQL raises grouping errors, include every nonaggregated column in GROUP BY (shown above) or use an appropriate SQL mode.

Notes and best practices:

  • LEFT JOIN keeps loans with zero payments; SUM over no rows yields NULL, so COALESCE(...,0) produces 0.
  • COALESCE is ANSI-standard (portable); MySQL also supports IFNULL.
  • Add indexes on loan.loan_id, settlement.loan_id and loan.collector_name to keep this query fast on larger data.
Member Avatar for Member #1129692

can't help ?

Member Avatar for Member #1129692

help me?

Member Avatar for hmondy

Try
sum(ifnull(settlement.amount,0)) as 'Total Received'

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.