0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Now that we know who answers and the individual scores of those answers,
-- let's try to figure out who is most effective overall.  For each user who
-- answered any questions from the target user, this query returns the number
-- and total score of those answers.

-- Note that the SUM is done twice, once for the SELECT (to produce the
-- value) and once in the ORDER BY, so we can list the most-helpful 
-- answerers first.

SELECT SUM(a.Score) as 'Total Score',
COUNT(au.Id) as '# Answers',
au.Id AS [User Link]

FROM Posts p 
JOIN Posts a ON p.Id = a.ParentId
JOIN Users au ON a.OwnerUserId = au.Id

WHERE p.PostTypeId = 1
  AND p.OwnerUserId = ##UserId##

-- We want one row per user:
GROUP BY au.Id
-- In order by total score, highest first:
ORDER BY SUM(a.Score) DESC

view current version

edited mar 15 15

created oct 6 14 Monica Cellio