How to calculate voting points in a Q&A website using MySQL query

Question

Hello all

I am trying to execute the following query

SELECT a.userid, COUNT(a.id) AS count_answers, SUM(v.yes_vote) as total_up_votes, SUM(v.all_vote - v.yes_vote) as total_down_votes, CAST(COUNT(a.id) + SUM(v.yes_vote) * 4 - SUM(v.all_vote) AS SIGNED) AS voting_points
FROM (answers a)
LEFT OUTER JOIN answers_vote_count v ON r.id = v.answer_id
GROUP BY a.userid
ORDER BY count_answers DESC

As you can see, I am trying to calculate the points received on answer received.

Points are calculated like -

• 1 point for every answer - COUNT(answers)
• 3 points for every up vote on answer - 3 * SUM(yes_vote)
• (-1) point for every down vote - SUM(all_vote) - SUM(yes_vote)

My trouble is - if an answer does not have a up_vote and down_vote, the points come out to be NULL. The COUNT(answers) part does not work properly.

What am I doing wrong over here?

Regards

Answer 1

Store pre-calculated score value in the questions table.

Answer 2

I got it. Since I am doing the LEFT JOIN with the voting, that is why the answers, for which no votes are found, turn up as NULL. This can be solved using the CASE WHEN ELSE in SQL query - by using 0 when the total IS NULL.