Order questions depending on answer votes

Question

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)

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.