Sum of parent and Sum of child being duplicated

Question

Hi I have 2 tables and I want to group by month

+-----------+----------+-------+
| Parent ID | Payments | Month |
+-----------+----------+-------+
|         1 |      100 |     1 |
|         2 |      120 |     1 |
|         3 |      130 |     2 |
+-----------+----------+-------+
+----------+------------+----------+
| Child ID | Parent Ref |     Sold |
+----------+------------+----------+
|        1 |          1 |       20 |
|        2 |          1 |       30 |
|        3 |          2 |       50 |
|        4 |          2 |       10 |
+----------+------------+----------+

Expected Output should be

+----------+------+-------+------------------------------------------+
| Payments | Sold | Month |           Notes (no need sql)            |
+----------+------+-------+------------------------------------------+
|      220 |  110 |     1 | <-220=sum(100+120), sum(110=20+30+50+10) |
|      130 |    0 |     2 | <-130=130, null or 0 doesnt matter       |
+----------+------+-------+------------------------------------------+

What I'm getting with my query i think is the parent is multiplying its sum. I'm using a live database so not really sure if its multiplying by the number of child, but its multiplying somewhere. All the child sum result are ok, all the parents arnt. I've compared this against my previous SQL to make sure. The reason why im not using the old sql is its extremely slow due to many db calls and php processing.

+----------+------+-------+---------------------------------------------------------------------+
| Payments | Sold | Month |                         Notes (no need sql)                         |
+----------+------+-------+---------------------------------------------------------------------+
|      880 |  110 |     1 | <-220=sum(100+120)*4 as there are 4 childrows, sum(110=20+30+50+10) |
|      130 |    0 |     2 | <-130=130, null or 0 doesnt matter                                  |
+----------+------+-------+---------------------------------------------------------------------+

My Query

Select sum(parent.Payments), sum(child.Sold) 
from parent, child 
where
parent.id = child.parent_ref group by parent.month

Was going to post an answer, but too slow... http://www.sqlfiddle.com/#!9/7645ad/4 — Nick, Mar 25 '20 at 01:09
No, the child wont have a month column, it gets the month from the parent — Patrick, Mar 25 '20 at 12:02
Thanks Nick! Guess thats really the only way to do it SELECT SUM(p.Payments) AS Payments, COALESCE(SUM(c.Sold),0) AS Sold, p.month FROM ( SELECT id, month, SUM(Payments) AS Payments FROM parent GROUP BY id, month ) p LEFT JOIN ( SELECT parent_ref, SUM(Sold) AS Sold FROM child GROUP BY parent_ref ) c ON c.parent_ref = p.id GROUP BY p.month — Patrick, Mar 25 '20 at 12:04
Found another answer, 2 queries then let the scripting language do its work (PHP in my case) — Patrick, Mar 25 '20 at 12:06

score 0 · Answer 1 · answered Mar 25 '20 at 01:03

I think that you need to pre-aggregate the child table for each parent, then do the final computation in the outer query:

select
    p.month,
    sum(p.payments) payments
    coalesce(sum(c.sold), 0) sold
from parents p
left join (select parent_ref, sum(sold) sold from children group by parent_ref) c
    on c.parent_ref = p.parent_id
group by p.month

Sum of parent and Sum of child being duplicated

1 Answers1