0

I have an answers table (ans_id, question_id , ans_date) and an answers_votes table (vote_id , user_id , ans_id , vote = 1 or -1).

How do I select from answers that have the big sum(vote) of votes that posted today. This means have todays date in ans_date.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Mostafa Elkady
  • 5,645
  • 10
  • 45
  • 69
  • 1
    Hmm... second question like this today. Homework perhaps? What have you tried thus far? Here's a similar question: http://stackoverflow.com/questions/2553776/mysql-join-problem – itsmatt Mar 31 '10 at 16:33

2 Answers2

2
select a.ans_id
     , a.question_id
     , a.ans_date
     , (select sum(vote) from answers_votes where ans_id = a.ans_id) as points
     , (select count(*) from answers_votes where ans_id = a.ans_id and vote = 1) as up_votes
     , (select count(*) from answers_votes where ans_id = a.ans_id and vote = -1) as down_votes
  from answers a
 where date(a.ans_date) = date(now())
 order by points desc

Although this now makes the points value somewhat redundant. It could be calculated just the same as doing up_votes - down_votes - provided a vote is always worth only 1 point up or down.

Peter Bailey
  • 105,256
  • 31
  • 182
  • 206
1

Something like this :

select answers.ans_id, count(*) as nb_votes
from answers
    inner join answers_votes on answers_votes.ans_id = answers.ans_id
where answers.ans_date = curdate()
group by answers.ans_id
order by count(*) desc
limit 10

should give you the 10 answers that have the most votes, and have been created today.

If you want more, or less, than 10, you can change the value in the limit clause.


Not tested, as I don't have your database -- so might contain a few mistakes...

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • Using COUNT(answers_votes) for the aggregate function will give you the number of votes. Use SUM(answers_votes.vote) to get the weight of the vote since answers_votes.vote can be +1 or -1. – Marcus Adams Mar 31 '10 at 16:57