I have four mysql tables client, transaction, other_loan and payment. I want to get the sum of load_amount and additional from table transaction + sum of amount from other_loan and subtract it to the sum of payment_amount in table payment. How can I achieve it?
Result I want:
> ID | Name | Amount
> 1 | Robin | 8718
> 2 | Reynaldo | 21
> 3 | Leomar | 0
My Tables: transaction
> tid | id| date | load_amount | additional
> 1 | 1 | 2018-12-01 | 90 | 0
> 2 | 1 | 2018-12-07 | 90 | 0
> 3 | 2 | 2018-12-08 | 49 | 2
table: other_loan
> oid | id| amount | date
> 1 | 1 | 7928 | 2018-12-10
> 2 | 1 | 750 | 2018-12-10
table: payment
> pid |id | payment_amount | date
> 1 | 1 | 50 | 2015-12-10
> 2 | 1 | 90 | 2015-12-10
> 3 | 2 | 30 | 2015-12-10
table: client
> id | Name |
> 1 | Robin |
> 2 | Cinderella |
> 3 | Leomar |
Because you have multiple transactions, other loan amounts and payments per customer, you can't do a straight JOIN of the tables to each other as it will cause replication of rows, resulting in incorrect values. Instead, we SUM all the values within each table on a client basis before doing the JOIN. Additionally, since some clients don't have entries in each table, you must use LEFT JOINs and COALESCE on the results so that empty rows don't cause SUMs to become NULL. This query should give you the results you want:
SELECT c.id, c.name,
COALESCE(t.transactions, 0) + COALESCE(o.amounts, 0) - COALESCE(p.payments, 0) AS amount
FROM client c
LEFT JOIN (SELECT id, SUM(load_amount) + SUM(additional) AS transactions
FROM transaction
GROUP BY id) t on t.id = c.id
LEFT JOIN (SELECT id, SUM(amount) AS amounts
FROM other_loan
GROUP BY id) o ON o.id = c.id
LEFT JOIN (SELECT id, SUM(payment_amount) AS payments
FROM payment
GROUP BY id) p ON p.id = c.id
GROUP BY c.id
Output (for your sample data):
id name amount
1 Robin 8718
2 Cinderella 21
3 Leomar 0