Q&A about the site for expert genealogists and people interested in genealogy or family history
-- Replace the user-id in the query as per the requirement select UserId,displayname,Tag, Sum(Questions_Or_Answers) Number_of_QuestionsOrAnswers ,Sum(Reputation) TotalReputation, CONVERT(decimal(12,3),Sum(Reputation)) / CONVERT(decimal(12,3), SUm(Questions_Or_Answers)) As ReputationPerPost from (select UserId ,displayname,tagname Tag, Questions_Or_Answers,Reputation from (select u.id 'UserId',u.displayname, case when ts.TargetTagName is not null then ts.TargetTagName Else t.Tagname End 'Tagname' , count(p.Id) As 'Questions_Or_Answers' ,(Sum(case when v.VoteTypeId=2 then 1 else 0 end)*10 - Sum(case when v.VoteTypeId=3 then 1 else 0 end) *2 )as 'Reputation' from users as u inner join posts as p on u.id=p.owneruserid --inner join posts as q on p.id=q.parentid JOIN PostTags AS pt ON pt.PostId = p.Id JOIN Tags AS t ON t.Id = pt.TagId Left join tagsynonyms ts on t.Tagname=ts.SourceTagName INNER JOIN Votes AS v ON v.PostId = p.Id where p.posttypeid in (1,2) and v.VoteTypeId in (2,3) group by u.id,u.displayname,t.tagname,ts.TargetTagName Union select u.id 'UserId',u.displayname, case when ts.TargetTagName is not null then ts.TargetTagName Else t.Tagname End 'Tagname' , count(p.Id) As 'Questions_Or_Answers' ,(Sum(case when v.VoteTypeId=2 then 1 else 0 end)*10 - Sum(case when v.VoteTypeId=3 then 1 else 0 end) *2 )as 'Reputation' from users as u inner join posts as p on u.id=p.owneruserid --inner join posts as q on p.id=q.parentid JOIN PostTags AS pt ON pt.PostId = p.parentid JOIN Tags AS t ON t.Id = pt.TagId Left join tagsynonyms ts on t.Tagname=ts.SourceTagName INNER JOIN Votes AS v ON v.PostId = p.Id where p.posttypeid in (1,2) and v.VoteTypeId in (2,3) group by u.id,u.displayname,t.tagname,ts.TargetTagName) a where Userid in ( 101651) ) a Group by UserId,displayname,Tag order by UserId