Mysql query - where clause with result of subquery

i am totally depressed with this query, which i am solving for many hours :(

SELECT m_order.id,
(SELECT SUM(price*amount) FROM m_order_item as item WHERE item.id_order = m_order.id) AS total
FROM `m_order`
WHERE total > 100


It returns Unknown column 'total' in 'where clause' constantly, but in result without this problematic where clause column with name "total" is totally ok and calculated.

Thanks for any help.

2 Answers
2

You can't use aliases in WHERE clauses. See the manual. Put it into a HAVING instead:

WHERE

HAVING

SELECT m_order.id,
(SELECT SUM(price*amount) FROM m_order_item as item WHERE item.id_order = m_order.id) AS total
FROM `m_order`
HAVING total > 100


Total is a column alias and is not visible for where condition .. where part is evaluated by bd engine before the evaluation of select clause

so you should or repeat the code

SELECT m_order.id,
( SELECT SUM(price*amount)
FROM m_order_item as item
WHERE item.id_order = m_order.id ) AS total
FROM `m_order`
WHERE ( SELECT SUM(price*amount)
FROM m_order_item as item
WHERE item.id_order = m_order.id ) > 100


or try using having
having filter the result of select

SELECT m_order.id,
( SELECT SUM(price*amount)
FROM m_order_item as item
WHERE item.id_order = m_order.id ) AS total
FROM `m_order`
HAVING total > 100


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.