User Counts of ratio of answer to question Rounded to the nearest integer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT 
COUNT(pvt.owneruserid) UserCount, 
          ROUND(CASE 
          WHEN pvt.Question <> 0 THEN
          
          cast(pvt.answer  as decimal(7,2)) 
          /
          cast(pvt.Question as decimal(7,2))
           
           
           END, 0) ratio
           
FROM   (SELECT p.owneruserid, 
               p.id, 
               pt.name 
        FROM   posts p 
               INNER JOIN posttypes pt 
                 ON p.posttypeid = pt.id
                 WHERE pt.ID in (1,2) and p.ownerUserID is not null) p 
       PIVOT (
       COUNT (p.id) 
       FOR p.name IN ([Question], [Answer])) AS pvt
GROUP BY 
      ROUND(CASE 
          WHEN pvt.Question <> 0 THEN
          
          cast(pvt.answer  as decimal(7,2)) 
          /
          cast(pvt.Question as decimal(7,2))
          
           END, 0)
ORDER BY 
        ROUND(CASE 
          WHEN pvt.Question <> 0 THEN
          
          cast(pvt.answer  as decimal(7,2)) 
          /
          cast(pvt.Question as decimal(7,2))
          
           END, 0)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached