I have a query here i got an error the alias says unknown column, whats wrong with this. I need to get the sum of 16_judge_61 , 16_judge_62 , 16_judge_63
SELECT s.contestant_number, c.contestant_name, j.judge_name,
SUM(CASE WHEN s.crit_id =17 AND s.judge_id = '61' THEN score END ) AS 16_judge_61,
SUM(CASE WHEN s.crit_id =17 AND s.judge_id = '62' THEN score END ) AS 16_judge_62,
SUM(CASE WHEN s.crit_id =17 AND s.judge_id = '63' THEN score END ) AS 16_judge_63,
(16_judge_61 + 16_judge_62 + 16_judge_63) as total //<--here is the problem
//<--i also tried (sum(16_judge_61) + sum(16_judge_62) + sum(16_judge_63))as total not working as well
FROM tbl_score s INNER JOIN tbl_contestant c ON s.contestant_number = c.contestant_number
INNER JOIN tbl_judges j ON j.judge_id = s.judge_id
WHERE c.gender = 'male'
AND c.con_id = s.con_id
AND j.judge_id =62
GROUP BY s.contestant_number
Your error is being caused by trying to use an alias in the same
SELECT clause in which it was defined. You can't do this, because the alias has not yet been computed.
The knee jerk response to this problem is to just add together the three
CASE statements for
16_judge_63. But you can improve on this by recognizing that the three conditions can be combined into a single
CASE statement using
WHERE IN for the three
Try this query:
SELECT s.contestant_number, c.contestant_name, j.judge_name, SUM(CASE WHEN s.crit_id = 17 AND s.judge_id IN ('61', '62', '63') THEN score END ) AS total FROM ...