SQL - Count query question

Question

I want to know how many users answerd a question, so I made that query :

    SELECT answer.idAnswer, 
           answer.title, 
           answercategory.label, 
           (SUM(1)) as nbAnswer
      FROM ANSWER
INNER JOIN answerCategory ON answer.idAnswerCategory = answercategory.idAnswerCategory
 LEFT JOIN answerUser ON answer.idAnswer = answerUser.idAnswer
  GROUP BY answer.idAnswer

Its almost working, the only thing that doesn't work is that it's giving me "one" answer if nobody answered the question (it means even if there are no records in answerUser). I would like to have zero instead of one in that case. If I add a "-1", when there is one answer, I'll get zero. Any idea how I can correct that?

Is this MySQL or SQLite?

OMG Ponies
– OMG Ponies

2011-02-26 04:25:10 +00:00
Commented Feb 26, 2011 at 4:25 — OMG Ponies
– OMG Ponies, Commented Feb 26, 2011 at 4:25

Zulan · Accepted Answer · 2011-02-26 04:26:10Z

2

Use COUNT(answerUser.idAnswer) instead of SUM(1). Count will ignore the NULL rows created by the LEFT JOIN.

answered Feb 26, 2011 at 4:26

Zulan

22.8k7 gold badges57 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mellamokb · Accepted Answer · 2011-02-26 04:24:26Z

1

Use COUNT(*) instead of SUM(1).

answered Feb 26, 2011 at 4:24

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Comments

stefan · Accepted Answer · 2011-02-26 04:25:31Z

1

SELECT answer.idAnswer, answer.title, answercategory.label, count(*) as nbAnswer
FROM answer
INNER JOIN answerCategory on answer.idAnswerCategory = answercategory.idAnswerCategory
LEFT JOIN answerUser ON answer.idAnswer = answerUser.idAnswer
GROUP BY answer.idAnswer

answered Feb 26, 2011 at 4:25

stefan

2,88623 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL - Count query question

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related