sql - Using SELECT CASE and SUM: MYSQL -

i can't figure out how sum , down votes on item while returning whether or not given user voted on item.

here's votes table:

item_id vote voter_id 1        -1     joe 1         1     bob 1         1     tom 3         1     bob 

for item_id=1 here's data want show:

if joe looking @ page:

total votes: 2 total down votes: 1 my_vote: -1 

bob:

total votes: 2 total down votes: 1 my_vote: 1 

here's code:

select max(case when voter_id = 'joe' , vote=1 1  when voter_id = 'joe' , vote='-1' -1 else 0 end) my_vote, item_id, sum(vote=1) yes, sum(vote='-1') no votes item_id=1 

the issue my_vote=0 if use max function , vote=-1 (joe's scenario). similarly, my_vote=0 if use min function , vote=1 (bob's scenario).

thoughts?

select   item_id,   max(case when voter_id = 'joe' vote else null end)   my_vote,   sum(vote= 1)   yes_votes,   sum(vote=-1)   no_votes   votes   item_id = 1 group   item_id 

or, possibly more flexible...

select   votes.item_id,   max(case when users.user_id not null votes.vote else null end)   my_vote,   sum(votes.vote= 1)   yes_votes,   sum(votes.vote=-1)   no_votes   votes left join   users     on  users.user_id = votes.voter_id     , users.user_id = 'joe'   votes.item_id = 1 group   votes.item_id