Mysql query - where clause with result of subquery

Question

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.

See: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Aug 24, 2018 at 13:31

Nick · Accepted Answer · 2018-08-24 13:44:00Z

3

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

edited Aug 24, 2018 at 13:44

answered Aug 24, 2018 at 13:41

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2018-08-24 13:43:05Z

1

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

answered Aug 24, 2018 at 13:43

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Mysql query - where clause with result of subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related