CODEWITHSUNDEEP
mysqlsql
AdminRoy
AdminRoy

Reputation: 105

How to do a conditional count

I have a table in which the query is working fine

SELECT answers.answer,answers.id,answers.user_id,COUNT(vote.vote)
              FROM user_answers AS answers
              LEFT JOIN user_answer_vote AS vote ON answers.id =vote.answer_id
              WHERE answers.question_id = $question_id
              GROUP BY answers.id ORDER BY  COUNT(vote.vote) DESC

The query is returning as expected but the problem is that the vote.vote column is having both -eve and +eve value I want it to count only +eve values how can i do that?

Upvotes: 0

Views: 45

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Use case..when statement

COUNT(case when vote.vote='+eve' then 1 end)

within the query

SELECT a.answer,a.id,a.user_id, 
       COUNT(case when v.vote='+eve' then 1 end)  
    -- SUM(v.vote='+eve') might be another alternative for the above COUNT
  FROM user_answers AS a
  LEFT JOIN user_answer_vote AS v
    ON a.id =v.answer_id
 WHERE a.question_id = $question_id
 GROUP BY a.id 
 ORDER BY COUNT(case when vote.vote='+eve' then 1 end) DESC
       -- SUM(v.vote='+eve') might be another alternative for the above COUNT

Edit (due to your comment) :

SELECT a.answer,a.id,a.user_id, 
       SUM(case when v.vote='+eve' then 1 else -1 end )
  FROM user_answers AS a
  LEFT JOIN user_answer_vote AS v
    ON a.id =v.answer_id
 WHERE a.question_id = $question_id
 GROUP BY a.id 
 ORDER BY SUM(case when v.vote='+eve' then 1 else -1 end ) DESC

Demo for aggregations above

Upvotes: 2

Related Questions