CODEWITHSUNDEEP
mysqlsql
aWebDeveloper
aWebDeveloper

Reputation: 38432

Rectify this query

Why the query is not working 

SELECT (sum(`result` = 1)/count(id) * 100) as `abc`,  
case 
  when `abc` > 80 then 'pass'
  when `abc` < 80 then 'fail'
end as `abcd` 
FROM `user_quiz_answers` WHERE `user_quiz_id` = 39

TABLE:

id          int(11)         AUTO_INCREMENT                                  
question_id     int(11)                                             
result      tinyint(1)

ERROR:

#1054 - Unknown column 'abc' in 'field list'

I have managed this but not the above one 

SELECT 
case 
  when (sum(`result` = 1)/count(id) * 100) > 80 then 'pass' 
  when (sum(`result` = 1)/count(id) * 100) < 80 then 'fail' 
end as `abcd` 
FROM `user_quiz_answers` WHERE `user_quiz_id` = 39

Upvotes: 0

Views: 88

Answers (1)

Mchl
Mchl

Reputation: 62395

Because you can't use column aliases as column names in the same query. Something like this would work

SELECT
  `abc`,
  case 
    when `abc` > 80 then 'pass'
    when `abc` < 80 then 'fail'
  end as `abcd` 
FROM (
  SELECT (sum(`result` = 1)/count(id) * 100) as `abc`
  FROM `user_quiz_answers` WHERE `user_quiz_id` = 39
) AS sq

Other, simpler way to do what you seem to want to do would be:

SELECT 
  IF((sum(`result` = 1)/count(id) * 100) > 80, 'pass','fail') as `abcd`
FROM `user_quiz_answers` WHERE `user_quiz_id` = 39

Upvotes: 3

Related Questions