Total and average number of votes per question and answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #posts (PostTypeId TINYINT, Count INT)
INSERT INTO #posts
SELECT PostTypeId, COUNT(*) FROM Posts
  WHERE PostTypeId IN (1, 2)
  GROUP BY PostTypeId

CREATE TABLE #votes (PostTypeId TINYINT, VoteTypeId TINYINT, Count INT)
INSERT INTO #votes
SELECT p.PostTypeId, v.VoteTypeId, COUNT(*)
  FROM Votes AS v INNER JOIN Posts AS p ON v.PostId = p.Id
  WHERE v.VoteTypeId IN (2, 3) AND p.PostTypeId IN (1, 2)
  GROUP BY p.PostTypeId, v.VoteTypeId

SELECT CASE v.PostTypeId WHEN 1 THEN 'Questions' ELSE 'Answers' END AS 'Post type',
       CASE v.VoteTypeId WHEN 2 THEN 'Up' ELSE 'Down' END AS 'Vote type',
  v.Count AS 'Total',
  CAST(v.Count / CAST(p.Count AS float) AS NUMERIC(36,2)) AS 'Average'
  FROM #votes AS v INNER JOIN #posts AS p ON p.PostTypeId = v.PostTypeId
  ORDER BY v.Count / CAST(p.Count AS float) DESC

Enter Parameters

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