I'm making statistic for 5 tables. I have made the example with one client data.
Sql fiddle link is here:
http://sqlfiddle.com/#!2/21585/2/0
And the query is:
SELECT period, loan_sum, covers, delay
FROM
(SELECT MAX(EXTRACT(YEAR_MONTH FROM psc.date)) AS period,
(SELECT SUM(psr2.payment) FROM payment_schedule_row AS psr2 WHERE psr.payment_schedule_id = psr2.payment_schedule_id) AS loan_sum,
(SELECT SUM(psc2.sum) FROM payment_schedule_cover AS psc2 WHERE psc.payment_schedule_id = psc2.payment_schedule_id) AS covers,
(SELECT SUM(psd2.delay) FROM payment_schedule_delay AS psd2 WHERE psr.id = psd2.payment_schedule_row_id) AS delay
FROM loan
INNER JOIN payment_schedule AS ps ON ps.loan_id = loan.id
INNER JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id
INNER JOIN payment_schedule_cover AS psc ON psc.payment_schedule_id = ps.id
WHERE loan.status = 'payed'
GROUP BY ps.id) AS sum_by_id
GROUP BY period
Result:
period | loan_sum | covers | delay
-----------------------------------
201407 | 384 | 422 | 0.07
Everything is right except the delay. It should be 0.11 (0.07 + 0.03 + 0.01)
So I have been trying to find the error from the query for days now. Maybe someone can tell me what I'm doing wrong.