Q&A for people looking to bypass life's everyday problems with simple tricks
SELECT Posts.Id as [Post ID], -- Unique Post ID Tags.TagName as [Searched Tag], -- Searched Tag COUNT(DISTINCT Answers.Id) as [Answer Count], -- Count unique answers SUM(Answers.Score) as [Total Answer Score], -- Sum of answer scores CAST(AVG(CAST(Answers.Score AS float)) as numeric(6,1)) as [Average Answer Score], Posts.Tags as [Tagged With], Posts.Title as [Title], Posts.Body as [Body], Posts.CreationDate as [Creation Date], Users.DisplayName as [User Name], Users.Location as [User Location], Users.Views as [User Views], Users.Reputation as [User Reputation], Posts.ViewCount as [Post View Count], Posts.Score as [Post Score], Posts.AnswerCount as [Total Answer Count], Posts.AcceptedAnswerID as [Accepted Answer ID] FROM Posts -- Joining tables to get necessary details JOIN PostTags ON PostTags.PostId = Posts.Id JOIN Tags ON Tags.Id = PostTags.TagId JOIN Users ON Users.Id = Posts.OwnerUserId LEFT JOIN Posts AS Answers ON Answers.ParentId = Posts.Id -- Getting related answers WHERE Tags.TagName LIKE '%screen-readers%' -- Filter posts with 'usability' tags GROUP BY Posts.Id, Tags.TagName, Posts.Tags, Posts.Title, Posts.Body, Posts.CreationDate, Users.DisplayName, Users.Location, Users.Views, Users.Reputation, Posts.ViewCount, Posts.Score, Posts.AnswerCount, Posts.AcceptedAnswerID ORDER BY SUM(Answers.Score) DESC;