CODEWITHSUNDEEP
mysqlsqlmysql-error-1054
mTuran
mTuran

Reputation: 1834

Why Can't I Use Defined Variable in Where Clause

My query is:

SELECT 
  offer, 
  (SELECT 
        AVG(offer) 
   FROM 
        project_bids
  ) as var1 
FROM 
  `project_bids` 
WHERE 
  offer > var1

It causes "#1054 - Unknown column 'var1' in 'where clause'" error. Can anybody expalain why gives that error ? (i know working sql but i want to learn why it fails)

Upvotes: 0

Views: 2857

Answers (3)

Haim Evgi
Haim Evgi

Reputation: 125614

you would have to move "var1" out of the where and put in it to a having statement

the where statement does not have access to columns created in the select statement

Upvotes: 1

subhash
subhash

Reputation: 286

The sequence of execution of clauses of a SELECT statement is mentioned here:

http://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/

Alias of an column can not be used in any clause except the last clause "ORDER BY".

Upvotes: 2

subhash
subhash

Reputation: 286

Write it as below:

SELECT offer, (SELECT AVG(offer) FROM project_bids) as var1 FROM project_bids WHERE offer > (SELECT AVG(offer) FROM project_bids)

Upvotes: 0

Related Questions