CODEWITHSUNDEEP
mysqlwhere-clause
Kirk Logan
Kirk Logan

Reputation: 783

Age calculation not working

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.

Upvotes: 0

Views: 127

Answers (3)

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 1

Bart Friederichs
Bart Friederichs

Reputation: 33511

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

Upvotes: 1

user2152812
user2152812

Reputation: 1

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

Upvotes: -1

Related Questions