Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.)

  1. Question has_many answers
  2. Answer belongs_to one question

The answer table is like:

Answer(id, answer, votes_up, votes_down)
share|improve this question

1 Answer 1

Something like:

SELECT q.*, SUM(a.votes_up) - SUM(a.votes_down) AS TotalVotes
FROM Questions AS q
JOIN Answers AS a ON a.QuestionID = q.QuestionID
GROUP BY q.QuestionID, q.Question, q.AnyOtherFieldFromThatTable
ORDER BY TotalVotes DESC;

I would encourage you to include q.QuestionID (or whatever you call that field) in your GROUP BY clause, for performance reasons.

share|improve this answer
    
So - use the appropriate formula. I wasn't sure whether you wanted the total score, or the total number of votes regardless of whether they were positive or negative. You may want the minus to be plus in the first line. –  Rob Farley Oct 13 '12 at 3:44
    
Let me notice that your notice on GROUP BY applies to MySQL - other RDBMSes should throw an error if q.QuestionID (and possibly all other q columns) are missing from the GROUP BY clause. –  dezso Oct 13 '12 at 9:18
    
But you can omit them from the SELECT clause to avoid that particular error. My point is that even if you're not outputting the QuestionID, it's still worth grouping by it. –  Rob Farley Oct 13 '12 at 10:37
    
hey, @RobFarley. Thanks for your answer, this is what I was expecting. –  Mauricio N. Ferreira Oct 15 '12 at 0:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.