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.
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?