CODEWITHSUNDEEP
mysql
czLukasss
czLukasss

Reputation: 750

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.

Upvotes: 0

Views: 46

Answers (2)

Nick
Nick

Reputation: 147146

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

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

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Related Questions