Stack Overflow Data Explorer real unanswered questions count

Question

I'm trying to query the great Data Explorer tool to ask all kinds of questions about unanswered questions on Stack Overflow.

The problem is that there's a vast inconsistency between the results I get in my queries and the results that Stack Overflow publishes on the main site.

Specifically, Stack Overflow declares that there are

651,080 questions with no answers

(source)

However when I run this simple query which should give me the same result on Stack Overflow's DB -

select count(*) from posts where posttypeid = 1 and answercount = 0

I get this result -

56873

Unless I've written the wrong query for what I would like to know, this number is far off what Stack Overflow's officially declare (for the better).

Can anyone please help me understand why I get this inconsistency?

Thanks!

Answer 1

1. A post with no answers that have a score of 1 or more or an accepted answer is considered "unanswered". In other words, a zero, or negatively scored answer doesn't mean the question is "answered".
2. Closed questions are not "unanswered".
3. The data explorer isn't entirely up to date, it'll always be a bit out of date (by a matter of days, generally), so even if you fix you're query, it'll only ever be "close".

Answer 2

The reasons given by Servy are valid, but they can't explain the disparity between 651,080 and 56,873. The real reason is that the questions that never had an answer have AnswerCount set to NULL, not to 0. The questions with AnswerCount = 0 are those that had an answer once, but not anymore (any answer[s] have been deleted). To pick both kinds, you can use ISNULL:

WHERE ISNULL(AnswerCount,0) = 0

As a side effect, you now know how to locate questions with deleted answers (even though you can't see those deleted answers).