Posts by number of close votes

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with post_votes as (
select p.id
     , count(v.id) as ct
     , p.score
     , coalesce(a.answerct, 0) as answerct
  from posts p
  left outer join ( select parentid, count(*) as answerct
                      from posts
                     where posttypeid = 2
                     group by parentid ) a
    on p.id = a.parentid
  join votes v
    on p.id = v.postid
 where v.votetypeid = 6
   and p.closeddate is null
 group by p.id, p.score, coalesce(a.answerct, 0)
       )
select ct as [# Close Votes]
     , count(*) as [# Posts]
     , sum(case when score < 0 then 1 else 0 end) as [Minus Score]
     , sum(case when score = 0 then 1 else 0 end) as [0 Score]
     , sum(case when score > 0 then 1 else 0 end) as [Plus Score]
  from post_votes
 where answerct = 0
 group by ct

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results