MySQL Query to get age from date of birth

Question

I have an issue with my query,

SELECT id, pseudo, nom, prenom, sexe, ville, 
       FLOOR(DATEDIFF (NOW(), date_naissance)/365) AS mAge 
FROM user 
WHERE sexe = 'Homme' AND mAge BETWEEN 18 AND 25 OR ville = 'Bordeaux'

It is supposed to return the matching user with the where condition. The problem is the following, mAge is not existing I get the following error :

1054 - Unknown column 'mAge' in 'where clause'

Looks like my alias is not working properly on the where condition.

If I remove the mAge WHERE CLAUSE, I successfully get the mAge alias.

I need you guys

Thanks in advance !

possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — Ryan, Jul 18 '13 at 22:04
just a thought, but try this: `...WHERE (sexe = 'Homme') AND (mAge BETWEEN 18 AND 25) OR ville = 'Bordeaux'` multiple AND and OR statements can be confusing. best practice to always use parenthesis to make it clear what you want. — amaster, Jul 18 '13 at 22:07
please also see http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql — amaster, Jul 18 '13 at 22:08

score 6 · Accepted Answer · answered Jul 18 '13 at 22:08

You can not use column aliases in WHERE clauses: http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html.

You will have to rethink your query or change to:

SELECT id, pseudo, nom, prenom, sexe, ville, 
   FLOOR(DATEDIFF (NOW(), date_naissance)/365) AS mAge 
FROM user 
WHERE sexe = 'Homme' AND 
FLOOR(DATEDIFF (NOW(), date_naissance)/365) BETWEEN 18 AND 25 
OR ville = 'Bordeaux'

Ps you may also want to have a look at your ANDs and ORs as well as you might want to include some brackets.

How about using `HAVING` like this `WHERE sexe = 'Homme' OR ville = 'Bordeaux' HAVING mAge BETWEEN 18 AND 25` ? — Prabowo Murti, Apr 15 '14 at 13:48

score 1 · Answer 2 · answered Jun 06 '14 at 12:33

I use this query to get age of the user (years and months)

SELECT 
username
,date_of_birth
,(PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m') , DATE_FORMAT(date_of_birth, '%Y%m') )) DIV 12 AS years
,(PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m') , DATE_FORMAT(date_of_birth, '%Y%m') )) MOD 12 AS months
FROM users

Result as of today:

chucknorris, 1979-04-10 00:00:00, 35, 2

MySQL Query to get age from date of birth

1054 - Unknown column 'mAge' in 'where clause'

2 Answers2

Linked