Ville Mattila wrote:
> I try to get a list of all Invoices with total sum of the invoice and
> paid sum of each invoices, as well as a customer name. I try following
> SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price)
> AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal,
> MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents
> ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON
> (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON
> (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY
> The query works fine, but multiples the total paid sum with the number
> of the matched InvoiceContents. If I remove the JOIN with
> InvoiceContents, the query works fine (except I can't get the total sum
> of the invoice).
, COALESCE(p.PaidTotal, 0.00) AS PaidTotal
, COALESCE(p.LastPayment, 'No payment made') AS LastPayment
FROM Invoices i
INNER JOIN (
, SUM(Amount * Price) AS InvoiceTotal
GROUP BY InvoiceID
) AS ic
ON i.ID = ic.InvoiceID
INNER JOIN Customers c
ON i.CustomerID = c.ID
LEFT JOIN (
, SUM(Amount) AS PaidTotal
, MAX(Payments.Date) AS LastPayment
GROUP BY InvoiceID
) AS p
ON i.ID = p.InvoiceID;
Just to explain, what I'm doing here is using two derived tables to get
the aggregate information for both payments and invoices. I have
changed some of your LEFT JOINs to INNER JOINs, because I could not see
a way that an invoice would *not* have a customer, or that an invoice
would *not* have any contents. However, I kept the LEFT JOIN on the
payments aggregate because it is likely that an invoice would not have a
payment associated with it.
You will notice that I have taken the aggregates (the SUM and MAX
functions) out of the main query and put them into the derived tables
(the subqueries in the FROM clause). This prevents the "multiplication"
effect you saw earlier.
Also, I have used the COALESCE function in the outer SELECT in order to
eliminate any possible NULL values from the results and replace those
with a correct value.
Hope this helps,
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: jay@stripped mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster