Perform operation on 2 aggregated fields in SQL

Question

I'm trying to show four columns in the query below: a count of total responses, a count of incorrect responses, and a % incorrect based off the two previous columns. The results will be grouped by the question_id.

SELECT 
    COUNT(correct) as total_resp,
    SUM(case when correct = 'f' then 1 else 0 end) as incor_resp,
    (incor_resp / total_resp) as percent_incor,
    question_id
FROM answers
WHERE student_id IN (
    SELECT id FROM students
    WHERE lang = 'es'
    LIMIT 50
)
GROUP BY question_id;

My question is, why doesn't the percent_incor definition in the above work? Do I not have access to total_resp and incor_resp to be able to perform operations off them for a 3rd field definition? If not, how would I be able to include this field in my output?

Thanks!

Answer 1

It's not possible to refer to other fields via their aliases in the same select. Either you need to repeat the expressions again or you can wrap the select in another select and compute it there:

SELECT total_resp, incor_resp, incor_resp/total_resp as percent_incor
FROM (
    SELECT 
        COUNT(correct) as total_resp,
        SUM(case when correct = 'f' then 1 else 0 end) as incor_resp,
        (incor_resp / total_resp) as percent_incor,
        question_id
    FROM answers
    WHERE student_id IN (
        SELECT id FROM students
        WHERE lang = 'es'
        LIMIT 50
    )
    GROUP BY question_id
) t;