CODEWITHSUNDEEP
mysqlsqloptimizationjoinsubquery
ACobbs
ACobbs

Reputation: 364

Selecting a summed value from a subquery that relies on a joined table

So I'm the lucky guy who gets to optimize a query for our application that's taking far too long for the data we're getting. The data we're looking for isn't significantly complex, but the crappy database design is making it a lot harder then it should be (which is great, because I'm the one who designed it about a year ago).

The general idea is we're trying to calculate the total sales (they buy something that increases their balance) and the total payments (they paid money against their balance) for each customer.

The tables:

Customers

Sales (invoices):

Payments (account_payments)

Transactions (invoice_transactions)

If a user makes a sale, the info is recorded in invoices and invoice_transactions, with invoice_transactions having the invoice_id of the invoices record that contains the customer_id.

If the user makes a payment, the info is recording in account_payments and invoice_transactions, with invoice_transaction having an invoice_id of NULL, and account_payments containing the transaction_id as well as the customer_id.

I know, it's horrible... And I thought I was being clever! Well, I thought the problem through, and came up with a decent solution:

SELECT SQL_NO_CACHE
    c.company,
    (SELECT SUM(amount) FROM sales),
    (SELECT SUM(amount) FROM payments)
FROM customers c
JOIN invoices i ON i.customer_id = c.id
JOIN invoice_transactions sales ON i.invoice_id = sales.id
JOIN account_payments ap ON ap.customer_id = c.id
JOIN invoice_transactions payments ON ap.transaction_id = payments.id

Which does absolutely nothing except give me an error "#1146 - Table 'db.sales' doesn't exist". I'm guessing it has something to do with sub queries being read prior to joins, but I honestly have no idea. And unfortunately I have no idea of another way to approach this problem... Much appreciated if anyone could give me a hand!

Upvotes: 0

Views: 351

Answers (2)

GarethD
GarethD

Reputation: 69789

I think the best approach would be to separate the the elements Sales and Payments into subqueries, your current method is cross joining all payments with all invoices before doing the aggregation. 

SELECT  c.ID, 
        c.Company, 
        COALESCE(Sales.Amount, 0) AS Sales, 
        COALESCE(Payments.Amount, 0) AS Payments
FROM    Customers c
        LEFT JOIN
        (   SELECT  Customer_ID, SUM(Amount) AS Amount
            FROM    Invoices
                    INNER JOIN invoice_transactions
                        ON Invoice_ID = Invoices.ID
            GROUP BY Customer_ID
        ) As Sales
            ON Sales.Customer_ID = c.ID
        LEFT JOIN
        (   SELECT  Customer_ID, SUM(Amount) AS Amount
            FROM    Account_Payments 
                    INNER JOIN invoice_transactions tr
                        ON tr.ID = Transaction_ID
            GROUP BY Customer_ID
        ) AS Payments
            ON Payments.Customer_ID = c.ID;

This will include customers with no invoices and no payments. You can change the left joins to inner joins to manipulate this.

Working Example on SQL Fiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your query doesn't make sense.

After doing all the joining, why not just use the tables in the "from" clause:

SELECT c.company, SUM(sales.amount),  SUM(payments.amount)
FROM customers c JOIN invoices i ON i.customer_id = c.id JOIN
     invoice_transactions sales ON i.invoice_id = sales.id JOIN
     account_payments ap ON ap.customer_id = c.id JOIN
     invoice_transactions payments ON ap.transaction_id = payments.id
group by c.company

Just giving a table an alias in the "from" clause does not make it available in subqueries elsewhere in the query.

I also added a GROUP BY clause, since your query seems to be aggregating by company.

Upvotes: 1

Related Questions