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.DisplayName) as [# Answers],
au.DisplayName

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.DisplayName
-- In order by total score, highest first:
ORDER BY SUM(a.Score) DESC

view current version

edited oct 6 14

created oct 6 14 Monica Cellio