MySQL - Use derived column names in the subsequent comparisons -

i have following 2 tables:

(i apologise being unable paste them here, have created snapshot , given link it):

table exams

+---------+------------+-------------+   | exam_id | exam_date  | description |   +---------+------------+-------------+   |       1 | 2016-06-01 | exam 1      |   |       2 | 2016-06-06 | exam 2      |   |       3 | 2016-06-07 | exam 3      |   |       4 | 2016-06-10 | exam 4      |   +---------+------------+-------------+   

table subjects

+------------+---------+-------------+   | subject_id | exam_id | result      |   +------------+---------+-------------+   |          1 |       1 | attended    |   |          2 |       1 | fail        |   |          3 |       2 | distinction |   |          4 |       2 | distinction |   |          5 |       3 | pass        |   |          6 |       3 | distinction |   |          7 |       4 | attended    |   |          8 |       4 | pass        |   +------------+---------+-------------+   

the possible values in "result" field are:

fail, attended, pass, distinction

the aim overall result exam depending on grades achieved in subordinate subjects exam - "fail" in 1 subject within exam mean overall result "fail". "attended" means result in subject indeterministic yet , unless other subjects have resulted in "fail" grade, result called "attended". likewise, "distinction" can attained when it's been achieved in subjects within exam.

query:

select exams.exam_id, exam_date, description,  @count_fail         :=  sum(result='fail')          count_fail,  @count_attended     :=  sum(result='attended')      count_attended,  @count_pass         :=  sum(result='pass')          count_pass,  @count_distinction  :=  sum(result='distinction')   count_distinction, case      when (@count_fail >= @count_attended , @count_fail >= 1)         'fail'     when (@count_attended >= @count_pass , @count_attended >= 1)         'attended'     when (@count_pass >= @count_distinction , @count_pass>= 1)             'pass'     else          'distinction'     end final_result exams  inner join subjects on exams.exam_id = subjects.exam_id group exam_id 

the results have stumped me...

+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ | exam_id | exam_date  | description | count_fail | count_attended | count_pass | count_distinction | final_result | +---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ |       1 | 2016-06-01 | exam 1      |          1 |              1 |          0 |                 0 |  distinction | |       2 | 2016-06-06 | exam 2      |          0 |              0 |          0 |                 2 |  fail        | |       3 | 2016-06-07 | exam 3      |          0 |              0 |          1 |                 1 |  distinction | |       4 | 2016-06-10 | exam 4      |          0 |              1 |          1 |                 0 |  pass        | +---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ 

please may ask what's going on here? how when conditions not met (it's evident sort of calculation taking place in @ variables, since output isn't gibberish)? small scale illustration of bigger scheme of things, unfortunately can't finer details of. however, want avoid using nested queries , stick joins as possible...

i'll grateful receive inputs , suggestions.

many thanks!

edit

the sql fiddle db , query here

the output expected was:

+---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ | exam_id | exam_date  | description | count_fail | count_attended | count_pass | count_distinction | final_result | +---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ |       1 | 2016-06-01 | exam 1      |          1 |              1 |          0 |                 0 |  fail        | |       2 | 2016-06-06 | exam 2      |          0 |              0 |          0 |                 2 |  distinction | |       3 | 2016-06-07 | exam 3      |          0 |              0 |          1 |                 1 |  pass        | |       4 | 2016-06-10 | exam 4      |          0 |              1 |          1 |                 0 |  attended    | +---------+------------+-------------+------------+----------------+------------+-------------------+--------------+ 

reasons being:

row 1: lowest grade possible (fail) achieved in @ least 1 of subjects , hence overall result must "fail"

row 2: both subjects in exam have been cleared distinction grade , hence overall result should "distinction"

row 3: 1 pass, 1 distinction. result can't distinction, however, isn't "fail" either. since both subjects under exam have been cleared (although 1 of them isn't distinction grade), result should "pass".

row 4: 1 attended, 1 pass. counts aren't enough conclude overall result "pass" (since "attended" indeterministic). therefore, overall result qualify "attended" (until point subjects within exam cleared "pass", or there's @ least 1 "fail", consequently leading overall result called "fail". stands, "distinction" not achievable since @ least 1 subject within exam has been registered lower pass grade)

here's solution shown in sqlfiddle without user defined variables

select exams.exam_id, exam_date, description,  sum(result='fail')          count_fail,  sum(result='attended')      count_attended,  sum(result='pass')          count_pass,  sum(result='distinction')   count_distinction, case      when (sum(result='fail') >= sum(result='attended') , sum(result='fail') >= 1)         'fail'     when (sum(result='attended') >= sum(result='pass')  , sum(result='attended') >= 1)         'attended'     when (sum(result='pass')  >= sum(result='distinction') , sum(result='pass') >= 1)             'pass'     else          'distinction'     end final_result exams  inner join subjects on exams.exam_id = subjects.exam_id group exam_id