Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Using alias in WHERE

    Hi everyone,

    I've an SQL as follows:

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thank you so much, Dave

    It works now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •