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

Question

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

try removing SUM on case statements then add Sum to (16_judge+.....) — Yuvraj Mudaliar
– Yuvraj Mudaliar, Commented Sep 11, 2016 at 13:47

Strawberry · Accepted Answer · 2016-09-11 15:10:12Z

2

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

edited Sep 11, 2016 at 15:10

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Sep 11, 2016 at 13:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yuvraj Mudaliar · Accepted Answer · 2016-09-11 13:49:00Z

0

Not sure why you have used SUM(CASE ...)

SELECT s.contestant_number, c.contestant_name, j.judge_name, 

    CASE WHEN s.crit_id =17 AND s.judge_id =  '61' THEN score END  AS 16_judge_61, 
    CASE WHEN s.crit_id =17 AND s.judge_id =  '62' THEN score END  AS 16_judge_62,
    CASE WHEN s.crit_id =17 AND s.judge_id =  '63' THEN score END  AS 16_judge_63,

    SUM(16_judge_61 + 16_judge_62 + 16_judge_63) as total 


    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

answered Sep 11, 2016 at 13:49

Yuvraj Mudaliar

3755 silver badges15 bronze badges

Comments

Shushil Bohara · Accepted Answer · 2016-09-11 13:49:34Z

0

You can use sub query for that just as following:

SELECT *, (16_judge_61+16_judge_62+16_judge_63) AS total FROM (

-------------Your wholequery----------------

) t

answered Sep 11, 2016 at 13:49

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related