0

I am receiving an error during my age calculation.

Here is my query:

SELECT (YEAR(CURDATE()) - YEAR(STR_TO_DATE(birthdate, '%m/%d/%Y'))) age, name
FROM pers 
WHERE age >= 50 
ORDER BY age DESC 
LIMIT 100;

Here is the error:

#1054 - Unknown column 'age' in 'where clause'

"age" will output just fine when i remove the where clause, it just will not allow me to use it as a condition and im not sure why.

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
Kirk Logan
  • 733
  • 2
  • 8
  • 23

3 Answers3

1

You cannot use column aliases in WHERE clauses. Use the entire expression or use the HAVING clause, although that is only applied after the ORDER BY (so it could be quite inefficient with large datasets).

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
1

try this

 SELECT (YEAR(CURDATE()) - YEAR(STR_TO_DATE(birthdate, '%m/%d/%Y'))) age, name
 FROM pers 
 WHERE (YEAR(CURDATE()) - YEAR(STR_TO_DATE(birthdate, '%m/%d/%Y')))  >= 50 
 ORDER BY age DESC 
 LIMIT 100;

you can use the aliace age in where clause

here an answer to if you can use aliaces in whre clause

Community
  • 1
  • 1
echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • Thanks for the help, the guy above beat you to it! I appreciate the resource be HAVING wont work well as its over a million rows of data. Thanks! – Kirk Logan Mar 10 '13 at 19:34
-1

Looks to me like you have one bracket too many, here : '%m/%d/%Y')))

user2152812
  • 1
  • 2
  • No he doesn't. The bracketing is fine. – Bart Friederichs Mar 10 '13 at 19:19
  • Right. I missed the closing bracket after the first `CURDATE`. However, it is probably a copy error, as he says that the query runs fine without the `WHERE`. – Bart Friederichs Mar 10 '13 at 19:28
  • Yes- haha- brackets have always been the cause of my most annoying sql problems! Maybe you can remove my downvote- i was just trying to be helpful, after all! – user2152812 Mar 10 '13 at 19:28
  • @user2152812: If the answer is incorrect then it's better to remove the answer than to remove the downvote. – David Mar 10 '13 at 20:27
  • Not so David- it is a matter of principle. Brackets are a perennial source of issues with sql statements, particularly ones that are very complex. There was a bracket problem- and I pointed it out- it might not have been his issue- but the bracketing was wrong and that usually will cause issues. – user2152812 Mar 10 '13 at 20:32