0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to improve their interpersonal communication skills

SELECT Count(c.postid)    QuestionCount, 
       Count(a.id)        AnswerCount_ByCommenter, 
       Sum(CASE 
             WHEN p.closeddate IS NOT NULL THEN 1 
             ELSE 0 
           END)           Closed_Count, 
       Avg(p.answercount) Avg_AnswerCount 
FROM   comments c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1 
       AND c.text LIKE '%WhatHaveYouTried%' 
        OR c.text LIKE '%what have you tried%' 
UNION ALL 
SELECT Count(c.postid)    QuestionCount, 
       Count(a.id)        AnswerCount_ByCommenter, 
       Sum(CASE 
             WHEN p.closeddate IS NOT NULL THEN 1 
             ELSE 0 
           END)           Closed_Count, 
       Avg(p.answercount) Avg_AnswerCount 
FROM   (SELECT DISTINCT userid, 
                        postid 
        FROM   comments) AS c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1

view current version

edited jan 11 13

created jan 11 13 conradfrix