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?

Answers


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

Need Your Help

Building NodeJs module with boost (or any other library for that matter)

c++ node.js boost module

I'm trying to build a nodejs module with C++ and Ubuntu 13.04 using some boost headers as shown below:

Cannot get if to evaluate the result of $? correctly

bash grep

So here's what I'm trying to do. I want to grep through a file for two strings and then print out to the user whether the strings are present.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.