Using alias in WHERE

pearl2 · #1 (**permalink**) 07-09-10, 11:21

Hi everyone,

I've an SQL as follows:

Quote:

SELECT ROUND((correct/completed) * 100, 2) AS percent_score FROM records WHERE member_id=10 AND percent_score > 50.00;

I get the error ERROR 1054 (42S22): Unknown column 'percent_score' in 'where clause'

How do I make the second condition "percent_score > 50.00" work?

Looking forward to your replies

dav1mo · #2 (**permalink**) 07-09-10, 11:29

Since the column is calculated in the select, there is no way to have it included in your where clause. What you can do is materialize that resultset, then you can query it. Something like:

Code:

select percent_score
   from (SELECT ROUND((correct/completed) * 100, 2) AS percent_score
               FROM records WHERE member_id=10) as a
where percent_score > 50.00;

Dave

pearl2 · #3 (**permalink**) 07-09-10, 11:44

Thank you so much, Dave

It works now

Internet Services

Web Development

Digital Marketing

Consumer Tech