There is a 1-to-many relationship between the posts
table and the votes
table, and they join on posts.id = votes.postid
. I would like to know how many posts have 1, 2, 3, 5, etc. votes.
Thanks to Waffles' most controversial posts query, it was trivial to write this:
declare @VoteStats table (PostId int, up int)
set nocount on
insert @VoteStats
select
PostId,
up = sum(case when VoteTypeId = 2 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId
set nocount off
select up, count(*)
from @VoteStats
group by up;
However, it runs in a full 22 seconds on the Stack Overflow data dump.
Am I overlooking an obvious way to speed this query up, perhaps avoiding the creation of the VoteStats table?
where VoteTypeId = 2
and then justcount(*)
the rows? That's essentially what you're currently asking. Or do you also have adown
column that goes the other way? \$\endgroup\$ – Clockwork-Muse Sep 7 '11 at 17:47