0

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)

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mTuran
  • 1,846
  • 4
  • 32
  • 58

3 Answers3

2

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".

subhash
  • 276
  • 1
  • 1
  • nice answer. But you could have just edited your other answer. – Kamal Nov 08 '10 at 12:50
  • I think the quoted order is wrong: the `SELECT` clause is resolved between `HAVING` and `ORDER BY` -- this is why only the `ORDER BY` can reference a column's `AS` clause ('alias'). – onedaywhen Nov 08 '10 at 14:10
  • 1
    ...in fact, I'm almost sure that the article details the format of a `SELECT` clause (i.e. the sequence the clauses must be written in code) rather than the order of execution. See the last comment (by 'Martin'). – onedaywhen Nov 08 '10 at 14:14
  • Here's the correct order of execution https://stackoverflow.com/a/3841804/1542774 – Stefan Gabos Jan 29 '19 at 13:45
1

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

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
0

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)

subhash
  • 276
  • 1
  • 1