MySQL Logic help

Question

I have the following query which works great. I have 2 questions....

1) Can I remove the inner join to order on the sub query and tie it back into the main query?

2) I need to factor in returns into all of the values generated. Price per item is ONLY stored in order_product. My table structure is as follows...

order

id  |  account_id  |  price  |  tax  |  shipping_price

order_product

order_id  |  product_id  |  vehicle_id  |  quantity  |  price

I need the system to support multiple RMAs per order...

order_return

id  |  status_id  |  order_id  |  account_id  |  timestamp

order_return_product

return_id  |  reason_id  |  product_id  |  vehicle_id  |  note  |  quantity

This query generates sales statistics and I need it to not factor in items which have been returned....

SELECT
                    SUM(price+shipping_price) AS total_sales,
                    COUNT(id) AS total_orders,
                    AVG(price+shipping_price) AS order_total_average,
                    (SELECT
                            SUM(quantity)
                        FROM `order_product`
                        INNER JOIN `order` ON (
                            `order`.id = order_product.order_id AND
                            `order`.created >= '.$startTimestamp.' AND
                            `order`.created <= '.$endTimestamp.' AND
                            `order`.type_id = '.$type->getId().' AND
                            `order`.fraud = 0
                        )
                    ) AS total_units,
                    SUM(price+shipping_price)/'.$dateDifference['total']['days'].' AS daily_average
                FROM `order`
                WHERE created >= '.$startTimestamp.' AND
                created <= '.$endTimestamp.' AND
                fraud = 0 AND
                type_id = '.$type->getId().'

Answer 1

i don't quite understand why you have formed the inner join; you are already querying the ORDER table, so you can refer to the current row in it. Perhaps the following would do?

SELECT
    SUM(price+shipping_price) AS total_sales,
    COUNT(id) AS total_orders,
    AVG(price+shipping_price) AS order_total_average,
    (SELECT
         SUM(quantity)
         FROM `order_product`
         WHERE `order`.id = order_product.order_id) AS total_units,
    SUM(price+shipping_price)/'.$dateDifference['total']['days'].' AS daily_average
FROM `order`
WHERE created >= '.$startTimestamp.' AND
      created <= '.$endTimestamp.'   AND
      fraud    = 0 AND
      type_id  = '.$type->getId().'

Edit: good point, I'd overlooked that. I'd suggest adding a subquery to find the returned total and subtracting that in the total_units calculation, perhaps along the following lines...

SELECT
SUM(price+shipping_price) AS total_sales,
COUNT(id) AS total_orders,
AVG(price+shipping_price) AS order_total_average,
((SELECT SUM(quantity)
      FROM `order_product`
      WHERE `order`.id = order_product.order_id) -
    (SELECT SUM (quantity)
         FROM order_return, order_return_product
         WHERE order_return.order_id = order.order_id AND
               order_return.id       = order_return_products.return_id))
   AS total_units,
SUM(price+shipping_price)/'.$dateDifference['total']['days'].' AS daily_average
FROM `order`
WHERE created    >= '.$startTimestamp.' AND
      created    <= '.$endTimestamp.'   AND
      fraud       = 0                   AND
      type_id  = '.$type->getId().'