SUM being duplicated on some JOINS

Question

I have the following SQL:

select
    `sites`.`number`,
    SUM(invoices.amount),
    SUM(payment_details.amount) as pda,
    COUNT(warrels.id) as warrels
from `sites`
left join `invoices`
    on `sites`.`id` = `invoices`.`invoiceable_id`
left join `warrels`
    on `sites`.`id` = `warrels`.`site_id`
left join `payment_details`
    on `invoices`.`id` = `payment_details`.`payable_id`
where `invoices`.`invoiceable_type` = 'App\\Models\\Site'
  and `sites`.`number` LIKE 'AU22%'
group by `sites`.`number`
order by `sites`.`number` asc

In most cases this returns correctly calculated sums. I calculate the overall balance in a simplified manner.

But when there are multiple payment_details associated with an invoice the SUM of the invoices.amount adds a duplicate to the overall sum. If I remove the join on payment_details, the sum of the invoices.amount always returns correctly.

Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Apr 09 '23 at 01:19
[Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) — philipxy, Apr 09 '23 at 01:20
@halfer [cheerfully] [as the editor before you] "an introductory line is fine" ... but not needed. It's noise. It's redundant. It is evident that "I have the following SQL"--because it's right there. Just because everyday conversation & related communications are typically redundant doesn't mean they need to be or are clearer for it. When I edit poor writing I remove as much as a can so there is as little bad left as I can manage. (And you were the editor before me, so you left a lot I removed.) (But as I hope you know, I'm thankful for your editing efforts.) — philipxy, Jul 02 '23 at 11:11
@philipxy: we probably agree more than you think. I do generally cut out of a lot of cruft. However it is my personal annoyance that people dump code without any preamble at all - think of the questions featuring only an error block, and the lazy question squashed into the overly-long title, in the way Redditors do. IMO a short preamble has a contextualising effect, preparing the reader for what is to come. I think that is useful and not merely a politeness. — halfer, Jul 02 '23 at 11:30
That said, I don't feel strongly about it, so I shalln't be offended if you roll back to the earlier version. `sql` is much more your tag than mine. I appreciate the note! — halfer, Jul 02 '23 at 11:30

score 1 · Answer 1 · answered Mar 22 '23 at 14:42

But there are some, when there are multiple payment_details associated with an invoice, where the SUM of the invoices.amount adds a duplicate to the overall sum.

You would typically pre-aggregate the payment details by invoice in a subquery before joining:

select s.number,
    SUM(i.amount),
    SUM(pd.amount) as pda,
    COUNT(w.id) as warrels
from sites s
left join invoices i on s.id = i.invoiceable_id and i.invoiceable_type = 'App\\Models\\Site'
left join warrels w  on s.id = w.site_id
left join (
    select payable_id, sum(pd.amount)
    from payment_details 
    group by payable_id
) pd on i.id = pd.payable_id
where s.number LIKE 'AU22%'
group by s.number
order by s.number

Note that, since you seem to intend left joins, I moved the condition on invoices from the where clause to the on clause of the join.

SUM being duplicated on some JOINS

1 Answers1