Raffy T Lawrence Raffy T Lawrence - 2 months ago 16
SQL Question

MySQL : How to get the sum columns by name of alias

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

Answer

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_61, 16_judge_62, and 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 judge_id values.

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 ...