MySQL: Ignore row when summing a field if another table contains a certain value
I have three tables, one called orders (containing only customer info), another called orders_total (containing order subtotals, discounts, and totals), and the last one called orders_products (containing names and prices of items being ordered). They all tied by a common field called orders_id.
I am trying to aggregate (sum) total sales revenue EXCEPT for orders that contain Item X (from the orders_products table), but for some reason the sum of sales revenues aren't adding up properly when I introduce the third table (orders_products). I know how to do the aggregation and the item exception separately with any two of three tables, but the complexity for me is combining both functions by introducing the third table. Here is my properly working query so far with just the two original tables:
SELECT o.orders_id, ot.orders_id, o.delivery_state, SUM(ot.revenue) AS ordersum FROM orders_total ot, orders o WHERE ot.orders_id = o.orders_id GROUP BY o.delivery_state ORDER BY ordersum DESC;
How would I implement the exception to ignore orders that contain Item X?
You can simply exclude any orders from your original query which are identified via a subselect:
SELECT o.orders_id, ot.orders_id, o.delivery_state, SUM(ot.revenue) AS ordersum FROM orders_total ot INNER JOIN orders o ON ot.orders_id = o.orders_id WHERE ot.orders_id NOT IN (SELECT orders_id FROM orders_products WHERE product_name = '?') GROUP BY o.delivery_state ORDER BY ordersum DESC;
Note that ? represents the product_name you are trying to exclude.
You also probably don't want orders_id information as part of your select, as that is meaningless data with regards to this aggregation.
For this query to run well make sure you have indexes on:
orders_products.product_name orders.delivery_date orders.orders_id orders_total.orders_id