List:General Discussion« Previous MessageNext Message »
From:Jay Pipes Date:May 26 2006 2:16pm
Subject:Re: Joining multiple tables with grouping functions
View as plain text  
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: jay@stripped	mob: +1 614 406 1267

Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
Thread
Joining multiple tables with grouping functionsVille Mattila26 May
  • Re: Joining multiple tables with grouping functionsPeter Brawley26 May
  • Re: Joining multiple tables with grouping functionsJay Pipes26 May