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.

Answer 1

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

Answer 2

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

Answer 3

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