Jump to content

SQL SUM() Calculates only one row in sub query


MulOnPomm

Recommended Posts

I'm making statistic for 5 tables. I have made the example with one client data.
 
loan:
loan.png
 
payment_schedule:
payment_schedule.png
 
payment_schedule_row
payment_schedule_row.png
 
payment_schedule_cover:
payment_schedule_cover.png
 
payment_schedlue_delay:
payment_schedule_delay.png
 
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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.