0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @TotalOnQuestions AS Int = (SELECT Count(*) FROM Comments c INNER JOIN Posts p ON p.Id = c.PostId WHERE p.PostTypeId = 1)
DECLARE @TotalOnAnswers AS Int = (SELECT Count(*) FROM Comments c INNER JOIN Posts p ON p.Id = c.PostId WHERE p.PostTypeId = 2)

SELECT
  q.Score AS [Score],
  (q.n + a.n) * 100.0 / (@TotalOnQuestions + @TotalOnAnswers) AS [% on posts],
  q.n * 100.0 / @TotalOnQuestions AS [% on questions],
  a.n * 100.0 / @TotalOnAnswers AS [% on answers],
  q.n AS [# on questions],
  a.n AS [# on answers]
FROM (
  SELECT
    c.Score AS [Score],
    Count(*) AS [n]
  FROM Comments c
  INNER JOIN Posts p ON c.PostId = p.Id
  WHERE PostTypeId = 1
  GROUP BY c.Score
) q INNER JOIN (
  SELECT
    c.Score AS [Score],
    Count(*) AS [n]
  FROM Comments c
  INNER JOIN Posts p ON c.PostId = p.Id
  WHERE PostTypeId = 2
  GROUP BY c.Score
) a ON q.Score = a.Score
ORDER BY Score ASC

fork query

edited nov 19 14

created nov 19 14 Gilles