Reputation from Posts Separated By Vote & Post Type

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

DECLARE @uid int = ##UserId##

-- Accept votes
SELECT 
  'Accepts' As [Type],
  (Count(*) * 15) As Total
FROM Votes JOIN Posts ON Votes.PostId=Posts.Id
WHERE Posts.OwnerUserId=@uid AND Posts.PostTypeId=2 AND Votes.VoteTypeId=1
GROUP BY Posts.OwnerUserId

UNION ALL

-- Up Votes (Q)
SELECT 
  'Question Up Votes' As [Type],
  (Count(*) * 10) As Total
FROM Votes JOIN Posts ON Votes.PostId=Posts.Id
WHERE Posts.OwnerUserId=@uid AND Posts.PostTypeId=1 AND Votes.VoteTypeId=2
GROUP BY Posts.OwnerUserId

UNION ALL

-- Up Votes (A)
SELECT 
  'Answer Up Votes' As [Type],
  (Count(*) * 10) As Total
FROM Votes JOIN Posts ON Votes.PostId=Posts.Id
WHERE Posts.OwnerUserId=@uid AND Posts.PostTypeId=2 AND Votes.VoteTypeId=2
GROUP BY Posts.OwnerUserId

UNION ALL

-- Down Votes (Q)
SELECT 
  'Question Down Votes' As [Type],
  (Count(*) * -2) As Total
FROM Votes JOIN Posts ON Votes.PostId=Posts.Id
WHERE Posts.OwnerUserId=@uid AND Posts.PostTypeId=1 AND Votes.VoteTypeId=3
GROUP BY Posts.OwnerUserId

UNION ALL

-- Down Votes (A)
SELECT 
  'Answer Down Votes' As [Type],
  (Count(*) * -2) As Total
FROM Votes JOIN Posts ON Votes.PostId=Posts.Id
WHERE Posts.OwnerUserId=@uid AND Posts.PostTypeId=2 AND Votes.VoteTypeId=3
GROUP BY Posts.OwnerUserId

Enter Parameters

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