question and comment count

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackoverflow.com/a/302428/578411

Stack Overflow

Q&A for professional and enthusiast programmers

with q_nc as
(
select count(*) as cnt
from posts p
left outer  join comments c on c.postid = p.id
where posttypeid = 1 -- Q
and answercount = 0
and c.id is null
),
q_c as
(
select count(*) as cnt
from posts p
where posttypeid = 1 -- Q
and answercount = 0
and exists (select 1 from comments c where c.postid = p.id)
),
q_a_c as
(
select count(*) as cnt
from posts p
where posttypeid = 1 -- Q
and answercount > 0
and exists (select 1 from comments c where c.postid = p.id)
),
q_a_nc as
(
select count(*) as cnt
from posts p
left outer  join comments c on c.postid = p.id
where posttypeid = 1 -- Q
and answercount > 0
and c.id is null
)



select (select q_nc.cnt 
from q_nc) as [Q but NC]
     , (select q_c.cnt 
from q_c) as [Q with C]
     , (select q_a_nc.cnt 
from q_a_nc) as [Q and A but no C]
     , (select q_a_c.cnt 
from q_a_c) as [Q and A with C]

Enter Parameters

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