1

So I got this view in mysql :

CREATE VIEW vote_question_totals
AS
SELECT question_id
, SUM((vote_question_type)='1') question_total_plus
, SUM((vote_question_type)='-1') question_total_minus
, count(*) question_total_mixed
,(SUM((vote_question_type)='1') - SUM((vote_question_type)='-1')) question_total_score
FROM vote_question
GROUP BY question_id;

and I'm doing a RIGHT JOIN with the question table using the question_id

but then I wanna ORDER by question_total_score and this is where it get problematical. The question with no vote get a question_total_score which is NULL and cant get properly ordered. (I would like my NULL row to be considered like a 0)

What should I do ?

  • Show the query with the JOIN. It can probably be easily solved using the COALESCE(question_total_score,0) function. – ypercubeᵀᴹ Jun 15 '14 at 23:12
1
SELECT 
   `vote_question_totals`.`question_id`, 
    COALESCE(question_total_score,0) AS `score`, 
   `question`.* 
FROM 
   `vote_question_totals` RIGHT JOIN `question` 
        ON vote_question_totals.question_id = question.question_id 
ORDER BY 
   `score` DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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