MySQL Forums
Forum List  »  Newbie

SQL SUM() Calculates only one row in sub query
Posted by: Peter Brawley
Date: December 02, 2014 02:58AM

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.

Options: ReplyQuote


Subject
Written By
Posted
SQL SUM() Calculates only one row in sub query
December 02, 2014 02:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.