phpmyadmin Question posted in
The official documentation can be found here.

How can I use a calculated value in the WHERE clause? – Phpmyadmin

I have the following SQL:

SELECT members.id, FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25) as age
FROM members
JOIN crew_cv ON members.id=crew_cv.user_id 
WHERE members.active=1 AND age>20 AND age<30 
ORDER BY crew_cv.last_name, crew_cv.first_name 
LIMIT 0,30

And I get the following error in phpMySQL:

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

How can I use the calculated value age in the WHERE clause?

Tags:

3

Answers


  1. 0

    In sql you can’t use alias in where clause (you must repeat the column code ) 

      SELECT members.id, FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25) as age
  FROM members
  JOIN crew_cv ON members.id=crew_cv.user_id 
  WHERE members.active=1 AND FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25)>20 
      AND age<FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25) 
  ORDER BY crew_cv.last_name, crew_cv.first_name 
  LIMIT 0,30

    but you could create a view 

    create view my_view as 
select   members.id, crew_cv.last_name, crew_cv.first_name , FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25) as age
 FROM members
  JOIN crew_cv ON members.id=crew_cv.user_id 
  WHERE members.active=1

    and then 

    SELECT id,  age, last_name, first_name
from  my_view 
where  age>20 AND age<30 
      ORDER BY last_name, first_name 
      LIMIT 0,30

    or apply the condition to an having clause

    In sql there the cluases are evalueated in a specific order
    the where condition is evaluated before select clause (so the column alias name in not know in this phase)
    instead the having clause is evaluated after the select clause

    Login or Signup to reply.
  2. 0 
    FROM clause
ON clause
OUTER clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
DISTINCT clause
ORDER BY clause
TOP clause

    This is the order of query excution, it means in your query the calculation is used (in where clause) before definition (in select clause).

    If you still want to place all in one query, this is my suggested query:

    SELECT members.id, temp.age
FROM members, 
     (SELECT user_id, crew_cv.last_name, crew_cv.first_name
             FLOOR(DATEDIFF('2019-10-25', crew_cv.dob) / 365.25) as age
      FROM crew_cv) as temp
WHERE members.id = temp.user_id  
      AND members.active=1
      AND temp.age > 20 AND temp.age < 30
ORDER BY temp.last_name, temp.first_name
LIMIT 0,30
    Login or Signup to reply.
  3. 0

    Maxim already answers the question but deleted the answer.

    MySQL extends the HAVING clause so it works even in queries with no aggregation. This allows it to filter a query using aliases — and that can be handy. So:

    SELECT m.id,
       FLOOR(DATEDIFF('2019-10-25', c.dob) / 365.25) as age
FROM members m JOIN
     crew_cv c
     ON m.id = c.user_id 
WHERE m.active = 1 
HAVING age > 20 AND age < 30 
ORDER BY c.last_name, c.first_name 
LIMIT 0, 30;

    MySQL does this because it tends to materialize subqueries. That adds overhead for reading and writing the data. In most other databases, you would just express this using a subquery or CTE, with no impact on performance. MySQL overloads HAVING as an alternative method.

    And, all that said, the / 365.25 is an approximation. A more accurate query would be:

    SELECT m.id,
       FLOOR(DATEDIFF('2019-10-25', c.dob) / 365.25) as age
FROM members m JOIN
     crew_cv c
     ON m.id = c.user_id 
WHERE m.active = 1 AND
      c.dob >= DATE('2019-10-25') - INTERVAL 30 YEAR AND
      c.dob <= DATE('2019-10-25') - INTERVAL 21 YEAR
ORDER BY c.last_name, c.first_name 
LIMIT 0, 30;

    This also has the advantage that it can use an index on (dob), if MySQL thinks that is appropriate.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search