How do I tally votes in MySQL?

I've got a database table called votes with three columns 'timestamp', 'voter', and 'voted_for'.

Each entry in the table represents one vote. I want to tally all of the votes for each 'voted_for' with some conditions.

The conditions are as follows:

• Each voter can vote only once, in the c开发者_StackOverflow社区ase of multiple votes by a single voter the most recent vote counts.

• Only votes made before a specified time are counted.

try this:

SELECT voted_for, count(*)
FROM votes v
INNER JOIN (SELECT Voter, Max(timestamp) as lastTime from votes group by Voter) A 
on A.Voter = v.voter and a.lasttime = v.timestamp 
WHERE timestamp < {date and time of last vote allowed}
Group by voted_for

the following may prove helpful:

drop table if exists users;
create table users
( 
user_id int unsigned not null auto_increment primary key,
username varbinary(32) not null,
unique key users_username_idx(username)
)engine=innodb;

insert into users (username) values 
('f00'),('foo'),('bar'),('bAr'),('bish'),('bash'),('bosh');

drop table if exists picture;
create table picture
( 
picture_id int unsigned not null auto_increment primary key,
user_id int unsigned not null, -- owner of the picture, the user who uploaded it
tot_votes int unsigned not null default 0, -- total number of votes 
tot_rating int unsigned not null default 0, -- accumulative ratings 
avg_rating decimal(5,2) not null default 0, -- tot_rating / tot_votes
key picture_user_idx(user_id)
)engine=innodb;

insert into picture (user_id) values 
(1),(2),(3),(4),(5),(6),(7),(1),(1),(2),(3),(6),(7),(7),(5);


drop table if exists picture_vote;
create table picture_vote
( 
picture_id int unsigned not null,
user_id int unsigned not null,-- voter
rating tinyint unsigned not null default 0, -- rating 0 to 5
primary key (picture_id, user_id)
)engine=innodb;

delimiter #

create trigger picture_vote_before_ins_trig before insert on picture_vote
for each row
proc_main:begin

declare total_rating int unsigned default 0;
declare total_votes int unsigned default 0;

 if exists (select 1 from picture_vote where 
    picture_id = new.picture_id and user_id = new.user_id) then
        leave proc_main;
 end if;

 select tot_rating + new.rating, tot_votes + 1 into total_rating, total_votes 
  from picture where picture_id = new.picture_id;

 -- counts/stats
 update picture set 
    tot_votes = total_votes,
    tot_rating = total_rating,
    avg_rating = total_rating / total_votes
 where picture_id = new.picture_id;

end proc_main #

delimiter ;

insert into picture_vote (picture_id, user_id, rating) values 
(1,1,5),(1,2,3),(1,3,3),(1,4,2),(1,5,1), 
(2,1,1),(2,2,2),(2,3,3),(2,4,4),(2,5,5),(2,6,1),(2,7,2),
(3,1,5),(3,2,5),(3,3,5),(3,4,5),(3,5,5),(3,6,5),(3,7,5);

select * from users order by user_id;
select * from picture order by picture_id;
select * from picture_vote order by picture_id, user_id;

SELECT voted_for,COUNT(DISTINCT voter)
FROM votes
WHERE timestamp < '2010-11-18 21:05:00'
GROUP BY voted_for