mysql - Redistribute votes while eliminating candidates -

consider thought. have ranking choice voting system, candidates doesn't meet threshold eliminated during different rounds. in example have 6 candidates running 2 seats in primary election. @ point have reached round 4 (with no winner) candidates 3 , 5 have been eliminated. in round candidate 4 being eliminated , his/hers votes redistributed candidates marked second or third choice on each ballot candidate 4 marked first choice.

we grab ballots candidate 4 first choice (which happens 4 ballots). this:

candidate 4:
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

the redistributed votes (in bold):
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

now problem: need write mysql query grabs these values , counts number of votes each given candidate recieves through redistribution. i.e. result mysql query should this:

candidate 1: 3 votes
candidate 6: 1 vote

edit: query need know candidates 3 , 5 eliminated heading next column find other candidate.

how write mysql query result?

been having play.

i think using current layout of data if done as:-

select coalesce(if(vote_candidate_b in (4,3,5), null, vote_candidate_b),                 if(vote_candidate_c in (4,3,5), null, vote_candidate_c),                 if(vote_candidate_d in (4,3,5), null, vote_candidate_d),                 if(vote_candidate_e in (4,3,5), null, vote_candidate_e),                 if(vote_candidate_f in (4,3,5), null, vote_candidate_f)) vote_candidate,                 count(*) votes vote_candidate_a = 4 group vote_candidate; 

this pretty fancypants has answered. not keen on doesn't seem easy read , (if amount of data large) inefficient.

i more inclined split data off different table structure, multiple rows per set of votes, 1 per candidate. far easier if system in stages of development (ie, generate new tables).

assuming existing data set follows:-

create table votes (     vote_id int not null auto_increment,     vote_candidate_a int,     vote_candidate_b int,     vote_candidate_c int,     vote_candidate_d int,     vote_candidate_e int,     vote_candidate_f int );  insert votes values (null, 4, 1, 6, 3, 2, 5), (null, 4, 3, 1, 6, 2, 5), (null, 4, 5, 6, 2, 3, 1), (null, 4, 3, 1, 5, 6, 2); 

my format generated follows:-

create table vote_orders (     id int not null auto_increment,     vote_id int,     vote_order int,     vote_candidate int );  insert vote_orders (id, vote_id, vote_order, vote_candidate) select null, vote_id, 1, vote_candidate_a votes union select null, vote_id, 2, vote_candidate_b votes union select null, vote_id, 3, vote_candidate_c votes union select null, vote_id, 4, vote_candidate_d votes union select null, vote_id, 5, vote_candidate_e votes union select null, vote_id, 6, vote_candidate_f votes; 

you use following votes. uses sub query highest vote hasn't been used , joins against data.

select vote_candidate, count(*) vote_orders inner join (     select vote_id, min(vote_order) min_vote_order     vote_orders     vote_candidate not in (4,3,5)     group vote_id ) b on a.vote_id = b.vote_id , a.vote_order = b.min_vote_order inner join (     select vote_id     vote_orders     vote_candidate  = 4     , vote_order = 1 ) c on a.vote_id = c.vote_id group vote_candidate 

sql fiddle here:-

http://www.sqlfiddle.com/#!2/7d48c/10

a hybrid solution (worst of both worlds!):-

select vote_candidate, count(*)  (     select vote_id, 1 vote_order, vote_candidate_a vote_candidate votes vote_candidate_a = 4     union     select vote_id, 2, vote_candidate_b votes vote_candidate_a = 4     union     select vote_id, 3, vote_candidate_c votes vote_candidate_a = 4     union     select vote_id, 4, vote_candidate_d votes vote_candidate_a = 4     union     select vote_id, 5, vote_candidate_e votes vote_candidate_a = 4     union     select vote_id, 6, vote_candidate_f votes vote_candidate_a = 4 ) inner join (     select vote_id, min(vote_order) min_vote_order          (         select vote_id, 2 vote_order, vote_candidate_b vote_candidate votes         union         select vote_id, 3, vote_candidate_c votes         union         select vote_id, 4, vote_candidate_d votes         union         select vote_id, 5, vote_candidate_e votes         union         select vote_id, 6, vote_candidate_f votes     )     vote_candidate not in (4,3,5)     group vote_id ) b on a.vote_id = b.vote_id , a.vote_order = b.min_vote_order group vote_candidate;