Mairo K,
This is a reply to your post at
http://forums.mysql.com/read.php?10,624520,624520#msg-624520.
First, Phorum software thought your post was spam. Then, when I released it from its spam holding cell, Phorum brilliantly closed it to replies.
In the query ...
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) AS delay
FROM loan
JOIN payment_schedule AS ps ON ps.loan_id = loan.id
JOIN payment_schedule_row AS psr ON psr.payment_schedule_id = ps.id
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;
... there are problems ...
1. The Group By operator in the subquery does not see aggregation inside the correlated sub-subquery sums. Those sums need to be moved out a level.
2. There's no aggregation for the outer query's Group By to group; it just functions as an Order By
3. A query like
select a,b,c sum(d) ... group by a can return arbitrary results for b and c unless a strictly 1:1 relationship holds between a and each of b and c, which looks unlikely to be the case in your subquery.
4. Correlated subqueries are inefficient, as yours illustrate with their two-stage joins
5. The `delay` correlated subquery doesn't join to anything
So move correlated subquery logic to the FROM clause, join the `delay` query, touch up the Group By clause, and we have ...
select psc.period, psc.sum, psr.payments, sum(psd.delay) as delay
from loan
join payment_schedule as ps on ps.loan_id = loan.id
join(
select payment_schedule_id, sum(payment) as payments
from payment_schedule_row
group by payment_schedule_id
) as psr on psr.payment_schedule_id = ps.id
join (
select payment_schedule_id, sum(sum) as sum, max( extract(year_month from date) ) as period
from payment_schedule_cover
group by payment_schedule_id
) psc on ps.id = psc.payment_schedule_id
join payment_schedule_row psr2 on ps.id = psr2.payment_schedule_id
join (
select payment_schedule_row_id, sum(delay) as delay
from payment_schedule_delay
group by payment_schedule_row_id
) as psd on psr2.id = psd.payment_schedule_row_id
where loan.status = 'payed'
group by psc.period, psc.sum, psr.payments;
Is that closer?
Edited 3 time(s). Last edit at 12/03/2014 10:51AM by Peter Brawley.