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
query:
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
Invoices.ID;
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).
Hi Ville!
Try this:
SELECT
i.*
, ic.InvoiceTotal
, c.Name
, COALESCE(p.PaidTotal, 0.00) AS PaidTotal
, COALESCE(p.LastPayment, 'No payment made') AS LastPayment
FROM Invoices i
INNER JOIN (
SELECT
InvoiceID
, SUM(Amount * Price) AS InvoiceTotal
FROM InvoiceContents
GROUP BY InvoiceID
) AS ic
ON i.ID = ic.InvoiceID
INNER JOIN Customers c
ON i.CustomerID = c.ID
LEFT JOIN (
SELECT
InvoiceID
, SUM(Amount) AS PaidTotal
, MAX(Payments.Date) AS LastPayment
FROM Payments
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,
--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED] mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
