CODEWITHSUNDEEP
mysqlsqlgroup-by
zalmai
zalmai

Reputation: 13

How can I find customers which are balanced in their debit and credits?

I am using MySQL and I have 3 tables which are customers, debits, and credits. Each customer has one or multiple debits and one or multiple credits. Now I want to calculate the total debit and total credit of each customer and just show those customers who are not balanced in total debit and total credit. Can anyone help me?

Upvotes: 0

Views: 70

Answers (1)

GMB
GMB

Reputation: 222612

One idea would be to left join two aggregate queries that compute the total credit and debit for each customer, and filter in the outer query.

Something like:

select cus.*
from customers cus
left join (select customer_id, sum(amount) total_amount from credits group by customer_id) cre
    on cre.customer_id = cus.customer_id
left join (select customer_id, sum(amount) total_amount from debits  group by customer_id) deb
    on deb.customer_id = cus.customer_id
where coalesce(cre.total_amount, 0) = coalesce(deb.customer_id, 0)

Upvotes: 1

Related Questions