SQL syntax help needed

[zzfritz]

I am revising the Monthly Sales & Tax Report to calculate the amount of non-taxed sales based on which orders had non-zero tax charges, rather than on which orders were shipped to zones outside the store state. (The current logic works for stores with tax obligations only in the home state, but I want this report column to be meaningful for all stores.)

 

To do this, the sql query requires a change that is a bit beyond my present capability, so I seek expert advice. (The query as described here is actually a mere skeleton of what is in the report script, where still more variables are conditionally used, but this is the essential part with values shown as literals.)

 

The necessary tables are orders (called o) and orders_total (called ot), which are joined on (o.orders_id = ot.orders_id). For each orders_id, the orders_total table contains one record for each component of the order total, namely the subtotal, tax, shipping, and perhaps others.

 

The query looks something like this

select sum(ot.value) from ORDERS o 

left join ORDERS_TOTAL ot on (o.orders_id = ot.orders_id) 

where ot.class="subtotal" 

and o.delivery_state<>"storezone"

 

and I want to replace the last phrase. I want to accumulate the orders_total subtotal records for the orders whose orders_total tax records are zero. The condition is

ot.class="taxes" and ot.value="0"

but I don't know the correct query syntax for this sort of recursion. For an expert, it will be obvious and the help will hasten my release of an improved report contribution.

[wizardsandwars]

OK, so let me see if I have this straight?

 

You wish to sum all of the order totals that have a tax of zero, correct?

[wizardsandwars]

Well, after looking at this for awhile, my suggestion is going to be to create a temprary table.

 

I come to this conclusion because you are trying to total the value in all colums where class = ot_total, yet you wish to not include rows in which the order_id has another row where the class = ot_tax, and the ot_value of THAT row is not zero.

 

I could do this with a DBMS that supported coralated sub quries, or running and sliding aggregates, buyI'm unable to fathom a query in MySQL that will do it directly.

 

You can easily do this by creating a temporary (or even permanent) table that includes all the fields you wish to look at in the same row.

[zzfritz]

Thanks for confirming my suspicion that MySQL couldn't do this in a single query. I didn't know the database terminology for correlated subqueries, or running and sliding aggregates ...

 

Now that I am sure the job has to be done in steps, I can see at least two ways to attack it.

[sqlguru]

Hello,

 

I think you can do this in one step. Try the query below:

 

SELECT sum( t0.value )

FROM orders_total t0, orders_total t1

WHERE t0.orders_id = t1.orders_id

and t0.title = "Total:"

and t1.title = "Tax:"

and t1.value = "0"

 

Is the result of this query what you are after?

 

I like SQL problems, so feel free to ask me about any others or I can work further on this one if the query above isn't what you need.

[wizardsandwars]

Ah, a self join. At the time, I hadn't even thought of this.

 

But upon further reflection, this makes perfect sense.

 

zzfritz, I think that this solution is going to do the trick. I look forward to its implementation.