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. Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.