How do I use WHERE on Computed Column?

Question

I'm trying to get some queries written for some integrity across two tables. The query is something like this

SELECT if( o.is_discounted !=1, o.item_cost, o.discounted_item_cost ) AS order_item_total,
SUM( oi.quantity * oi.price ) AS item_total
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE order_item_total != item_total
GROUP BY o.id

I've definitely used aliases to such columns in the past so I'm not sure why in this case it's telling me order_item_total is not a column.

duplicate of http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error [from mySQL docs](http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html): Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. — xQbert, Apr 16 '12 at 16:49
@xQbert You are correct, but the answer this isn't as nice as the one here, as far as this specific query goes. It's funny I've never tried this before - I thought had. But apparently it was just in ORDER BY and HAVING clauses — Dave Stein, Apr 16 '12 at 17:08

score 8 · Accepted Answer · edited Apr 16 '12 at 16:57

8

Use having on aggregated columns.

SELECT if(o.is_discounted != 1, o.item_cost, o.discounted_item_cost) order_item_total,
  SUM(oi.quantity * oi.price) item_total
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
HAVING order_item_total != item_total

edited Apr 16 '12 at 16:57

Mosty Mostacho

42,742
16
96
123

answered Apr 16 '12 at 16:50

user1336827

1,728
2
15
30

1

close having & group by are backwards. – xQbert Apr 16 '12 at 16:54
Yup. This is the best solution – Mosty Mostacho Apr 16 '12 at 16:58

score 4 · Answer 2 · answered Apr 16 '12 at 16:47

4

try wrapping the whole thing in another SELECT query.

SELECT *
FROM 
(
    SELECT if( o.is_discounted !=1, o.item_cost, o.discounted_item_cost ) AS order_item_total,
    SUM( oi.quantity * oi.price ) AS item_total
    FROM orders o
    INNER JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id
) x
WHERE X.order_item_total != X.item_total

answered Apr 16 '12 at 16:47

Taryn

242,637
56
362
405

2

This one actually works off the bat but waiting to mark as answer to see if there's one without "fake" subquery – Dave Stein Apr 16 '12 at 16:53
@DaveStein There are probably better ways of doing this. This is def a quick and dirty way to get the job done – Taryn Apr 16 '12 at 16:59

JonH · Answer 3 · 2012-04-16T16:58:44.077

4

WHERE comes before SELECT when manipulating data. So you need WHERE if( o.is_discounted !=1, o.item_cost, o.discounted_item_cost ) != SUM( oi.quantity * oi.price )

The other way to handle this is use a subquery

SELECT 
  ..
 FROM 
   ( //your query here
   ) t
//now you can use your aliases
WHERE t.order_item_total != t.item_total

Here with the having:

SELECT if( o.is_discounted !=1, o.item_cost, o.discounted_item_cost ) AS order_item_total,
SUM( oi.quantity * oi.price ) AS item_total
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE 1
GROUP BY o.id
HAVING order_item_total != item_total

edited Apr 16 '12 at 16:58

answered Apr 16 '12 at 16:47

JonH

32,732
12
87
145

I had tried that earlier and then it told me "Invalid use of group function" – Dave Stein Apr 16 '12 at 16:50
1

@DaveStein - Just noticed you are using a GROUP BY clauses, just change the WHERE to a HAVING statement and it should work for you. – JonH Apr 16 '12 at 16:51
`where X != sum()` ? Are you sure about that? – Mosty Mostacho Apr 16 '12 at 16:52
@MostyMostacho did you read my comment??? I didn't realize he was using a group by so I mentioned to change it using the HAVING clause or just wrap it in a sub query. – JonH Apr 16 '12 at 16:53
@JonH it doesn't know about `o.is_discounted` when I added `HAVING` after the GROUP BY – Dave Stein Apr 16 '12 at 16:54
@DaveStein can you wrap it all in a subquery and then reference the aliases? – JonH Apr 16 '12 at 16:55
@MostyMostacho yeah i keep all the summed data in the order table, so sum of items, taxes, shipping, etc.. I am writing this to double check that it matches my actual order items table. – Dave Stein Apr 16 '12 at 16:55
@JonH that is the answer below which I also commented on – Dave Stein Apr 16 '12 at 16:56

How do I use WHERE on Computed Column?

3 Answers3