CODEWITHSUNDEEP
sqlmysqlleft-join
Sami Samiuddin
Sami Samiuddin

Reputation: 457

Left join duplicates issue

I have 3 tables which I'm trying to JOIN together and get the SUM.

Invoices Invoice Items Payments

An invoice can have multiple Items. I'm able to join these together and get correct sum.

The invoice can also have multiple payments, this is where the issue is. Payments table rows seems to duplicate and return wrong total.

invoices

id discount_rate
40 0.25
41 0.25
42 0.1

invoice_items

invoice_id qty cost vat_rate
Cell 1 Cell 2 Cell 2 Cell 2
Cell 3 Cell 4 Cell 2 Cell 2

I've successfully joined these two tables and got the correct result:

SELECT 
    invoices.id,
    SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final
FROM
    invoices
        JOIN
    invoice_items ON invoices.id = invoice_items.invoice_id
GROUP BY invoice_id;

enter image description here

The problem starts when I join the payments (one invoice can have multiple payments) table to it. payments

payments

invoice_id amount
Cell 1 Cell 2
Cell 3 Cell 4

Payments table with data: enter image description here

Query:

with 
paymentSumAggr as (
select invoice_id, sum(amount) as paymentSum from payments
group by invoice_id
)

select      
invoices.id,
    SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final,
    sum(p_sum.paymentSum) as payments_sum
from
invoices
        left join
    invoice_items ON invoices.id = invoice_items.invoice_id
    left join (select * from paymentSumAggr) as p_sum on invoices.id = p_sum.invoice_id
    group by invoices.id

Result from query: enter image description here

I've tried joining these in many different way, tried subqueries and grouping aswell.

Upvotes: 0

Views: 63

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Since you have already summed the payments for each invoice you need not sum it up again. Please try the below query:

with 
paymentSumAggr as (
select invoice_id, sum(amount) as paymentSum from payments
group by invoice_id
)

select      
invoices.id,
    SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final,
    p_sum.paymentSum as payments_sum
from
invoices
        left join
    invoice_items ON invoices.id = invoice_items.invoice_id
    left join paymentSumAggr as p_sum on invoices.id = p_sum.invoice_id
    group by invoices.id

Upvotes: 1

Related Questions