CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

Avoiding multiple subqueries to increase query speed

I have one table that lists orders we receive. One row per order.

A second table records transactions associated with the order.

I am trying to produce a report that shows one order per row and on each row to display the total value of the various types of transactions.

My query is:

select 
orders.orderID, 
customers.username, 
(select sum(amount) from transactions where transactions.orderID=orders.orderID and transactionType IN (17) ) cost,
(select sum(amount) from transactions where transactions.orderID=orders.orderID and transactionType IN (18,19,20) ) surcharges,
(select sum(amount) from transactions where transactions.orderID=orders.orderID and transactionType IN (21,22) ) payments_received
from orders 
left join customers on orders.customerID=customers.customerID 
order by orderID

But this is pretty slow. I have indexes on the appropriate columns.

Can I avoid doing three subqueries and instead just run one query that spits out cost, surcharges and payments_received?

Upvotes: 0

Views: 80

Answers (1)

gvee
gvee

Reputation: 17161

Something like this should do it:

SELECT orders.orderid
     , customers.username
     , Sum(CASE WHEN transactions.transactiontype IN (17        ) THEN transactions.amount END) As cost
     , Sum(CASE WHEN transactions.transactiontype IN (18, 19, 20) THEN transactions.amount END) As surcharges
     , Sum(CASE WHEN transactions.transactiontype IN (21, 22    ) THEN transactions.amount END) As payments_received
FROM   orders
 LEFT
  JOIN customers
    ON customers.customerid = orders.customerid
 LEFT
  JOIN transactions
    ON transactions.orderid = orders.orderid
GROUP
    BY orders.orderid
     , customers.username
ORDER
    BY orders.orderid

Upvotes: 3

Related Questions