MulOnPomm Posted December 1, 2014 Share Posted December 1, 2014 I'm making statistic for 5 tables. I have made the example with one client data. loan: payment_schedule: payment_schedule_row payment_schedule_cover: payment_schedlue_delay: 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 Sqlfiddle link: http://sqlfiddle.com/#!2/21585/2/0 Result for the query: 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. Link to comment https://forums.phpfreaks.com/topic/292824-sql-sum-calculates-only-one-row-in-sub-query/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.