CODEWITHSUNDEEP
mysqlsql
Alessandro Desantis
Alessandro Desantis

Reputation: 14343

SUM on calculated field is doubled

I have an invoices table. Each invoice has many invoice_items and transactions (or, if you prefer, "payments"). For each invoice, I want to calculate the paid amount (i.e. the sum of its transactions' amounts), and the total amount (i.e. the sum of the items' amounts).

I've written the following query:

SELECT 
  invoices.*,
  SUM(transactions.amount_cents) AS amount_paid,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total
FROM invoices
RIGHT JOIN transactions 
  ON invoices.id = transactions.invoice_id
RIGHT JOIN invoice_items 
  ON invoices.id = invoice_items.invoice_id

However, the total field's value is multiplied by the number of transactions, for some reason (e.g. if I have an invoice with a total amount of 20, and 2 transactions, then the total field is 40).

If I remove all references to transactions from the query:

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total
FROM invoices
RIGHT JOIN invoice_items 
  ON invoices.id = invoice_items.invoice_id

The total field is returned correctly...

I'm not really an expert on the matter, so I'm probably doing something very stupid. I've already tried different combinations of JOINs, but with no result.

Any hints?

EDIT: SEMI-FINAL SOLUTION

I was trying to get the unpaid invoices, so here's the final query:

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total,
t.amount_paid
FROM invoices
LEFT JOIN invoice_items 
  ON invoice_items.invoice_id = invoices.id
LEFT JOIN (
  SELECT 
    invoice_id, 
    SUM(transactions.amount_cents) AS amount_paid
  FROM transactions 
  GROUP BY invoice_id
) t
  ON invoices.id = t.invoice_id
GROUP BY invoices.id
HAVING amount_paid >= total;

EDIT: AFTER REALIZING I DON'T NEED A JOIN...

I don't really need to get the associated data. I just want to know if the invoice is paid or unpaid. So I ended up using a simple where:

SELECT `invoices`.* 
FROM `invoices` 
WHERE (
  (
    SELECT COALESCE(SUM(t.amount_cents), 0)
    FROM transactions t 
    WHERE t.invoice_id = invoices.id
  )
  >=
  (
    SELECT COALESCE(SUM(i.quantity * i.price_cents), 0)
    FROM invoice_items i
    WHERE i.invoice_id = invoices.id
  )
)

But thanks to everyone who helped.

Upvotes: 1

Views: 357

Answers (2)

DRapp
DRapp

Reputation: 48139

Your duplication is based on Cartesian result... as you pointed out, one invoice has multiple transactions, so the transaction amount is getting counted every time it exists. You probably need sub-queries from respective tables to get their totals on their own grouped by the invoice ID...

SELECT 
      invoices.*,
      Payments.Amount_Paid,
      Items.Items_Total
   FROM 
      invoices
         JOIN ( select
                      T.Invoice_ID, 
                      sum( T.amount_cents ) amount_paid
                   from 
                      transactions T
                   group by
                      T.Invoice_ID ) Payments
            on Invoices.id = Payments.Invoice_ID
         JOIN ( select
                      II.Invoice_ID, 
                      sum( II.quantity * II.price_cents) AS Items_total
                   from 
                      Invoice_Items II
                   group by
                      II.Invoice_ID ) Items
            on Invoices.id = Items.Invoice_ID

Upvotes: 2

Steph Locke
Steph Locke

Reputation: 6146

You can use a subquery to aggregate the transactions up to one row per invoice_id so that it doesn't cause extra rows to occur. Depending on volumes, you may want to hive the subquery off into a temporary table and reference the temporary table.

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total,
t.Amount_Paid
FROM invoices
LEFT JOIN invoice_items 
  ON invoice_items.invoice_id = invoices.id
LEFT JOIN (select invoice_id, SUM(transactions.amount_cents) as Amount_Paid from
transactions group by invoice_id) t
  ON invoices.id = t.invoice_id
GROUP BY invoices.id

Upvotes: 2

Related Questions