scholzr scholzr - 6 months ago 15
SQL Question

MySQL JOIN with multiple tables and SUMS

I am trying to create a query that will take information out of four tables for a billing system that I am creating. I have the following tables:

Table Invoice

InvoiceID (PK)
ClientID
Date
Status
...


Table Client

ClientID (PK)
ClientName
...


Table InvoiceItem

ItemID (PK)
InvoiceID
Amount
...


Table Payments

PaymentID (PK)
InvoiceID
Amount
...


I need to create a query where I can access information from the Invoice table along with the client name, and the sum of all invoice items and payments associated with the invoice.

I have tried the following:

SELECT
Invoice.InvoiceID,
Invoice.`Date`,
Invoice.Terms,
Invoice.DateDue,
Invoice.Status,
Client.ClinicName,
SUM(InvoiceItem.Amount),
SUM(Payment.PaymentAmount)
FROM Invoice
JOIN (Client, InvoiceItem, Payment) ON
(Client.ClientID=Invoice.ClientID AND
InvoiceItem.InvoiceID=Invoice.InvoiceID AND
Payment.InvoiceID=Invoice.InvoiceID)


And while this kind-of works, it is multiplying the SUM() by the number of records used to get the sum (i.e. if there are two payments - 800,400 - It gives me (800+400)*2 -- 2400). I am guessing that there is something with how I am using the join, and I have honestly never had to use join for more than one table, and I would always use GROUP BY, but I can't seem to get that to work correctly.

To make matters worse, I have been lost to the world of vb.net/MSSQL client-side programming for the past several years, so my MySQL is rather rough.

Answer

Your problem is that you can't aggregate over two independent tables at once in a single query. However you can do it using subqueries.

SELECT Invoice.InvoiceID, Invoice.`Date`, Invoice.Terms, Invoice.DateDue, Invoice.Status, Client.ClinicName, InvoiceItemSum.SumOfAmount, PaymentSum.SumOfPaymentAmount
  FROM Invoice
  INNER JOIN Client ON Client.ClientID = Invoice.ClientID
  INNER JOIN (
    SELECT InvoiceID, SUM(Amount) AS SumOfAmount
      FROM InvoiceItem
      GROUP BY InvoiceID
  ) InvoiceItemSum ON InvoiceItemSum.InvoiceID = Invoice.InvoiceID
  INNER JOIN (
    SELECT InvoiceID, SUM(PaymentAmount) AS SumOfPaymentAmount
    FROM Payment
    GROUP BY InvoiceID
  ) PaymentSum ON PaymentSum.InvoiceID = Invoice.InvoiceID