Question, answer and comment counts for each user

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select
  Id,
  IsNull(sum(QuestionCount),0) as Questions,
  IsNull(sum(AnswerCount),0) as Answers,
  IsNull(sum(CommentCount),0) as Comments
from (
  select 
    u.Id, 
    count(*) as QuestionCount, 
    null as AnswerCount,
    null as CommentCount
  from
    Posts as p, 
    Users as u 
  where 
    p.OwnerUserId = u.Id and
    p.PostTypeId = 1
  group by
    u.Id
  union all
  select 
    u.Id,
    null as QuestionCount,
    count(*) as AnswerCount,
    null as CommentCount
  from
    Posts as p, 
    Users as u 
  where 
    p.OwnerUserId = u.Id and
    p.PostTypeId = 2
  group by
    u.Id
  union all
    select 
    u.Id,
    null as QuestionCount,
    null as AnswerCount,
    count(*) as CommentCount    
  from
    Comments as c, 
    Users as u 
  where 
    c.UserId = u.Id
  group by
    u.Id) temp
group by
  Id
order by 
  Id;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results