0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackoverflow.com/questions/297236/how-to-calculate-statistics-on-stackoverflow/313188#313188

Q&A for professional and enthusiast programmers

select year(v.creationdate)
     , count(*) as total
     , sum(case 
           when v.votetypeid = 2 then 1
           else 0
           end ) as upvotes
     , sum(case 
           when v.votetypeid = 3 then 1
           else 0
           end ) as downvotes
     , sum(case 
           when v.votetypeid = 2 and t.tagname ='c#' then 1
           else 0
           end ) as [c# up votes]
     , sum(case 
           when v.votetypeid = 3 and t.tagname ='c#' then 1
           else 0
           end ) as [c# down votes]
     , sum(case 
           when v.votetypeid = 2 and t.tagname ='java' then 1
           else 0
           end ) as [java up votes]
     , sum(case 
           when v.votetypeid = 3 and t.tagname ='java' then 1
           else 0
           end ) as [java down votes]
from votes v
inner join posttags pt on v.postid = pt.postid
inner join tags t on t.id = pt.tagid
where t.tagname in ('c#', 'java')
and v.votetypeid in (2, 3)
group by year(v.creationdate)

fork query

edited dec 22 15

created dec 22 15 rene