How to vote in MySQL?

Question

I have a database table called votes with three columns "timestamp", "voter" and "voted_for".

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

The conditions are as follows:

• Each voter can vote only once, in case of several votes from one voter, the most recent vote is counted.

• Only votes made before the specified time are counted.

Answer 1

try the following:

 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 

Answer 2

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

Answer 3

The following may be useful:

 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;