Q&A for administrators, end users, developers and designers for Craft CMS
SELECT TOP 360 /* Need to look at multiple tables unioned together; that outcome is called TableEntry */ TableEntry.Id as [Post Link], TableEntry.OwnerUserId as [User Link], Sum(TableEntry.SelfAnswers) as SelfAnswers, Sum(TableEntry.SelfComments) as SelfComments, Sum(TableEntry.NonSelfAnswers) as NonSelfAnswers, Sum(TableEntry.NonSelfComments) as NonSelfComments FROM ( SELECT /* table for selecting posts where the owner self-answered */ q.Id as Id, q.OwnerUserId as OwnerUserId, Count(p.Id) as SelfAnswers, 0 as SelfComments, /* must have matching columns so 0 for comments column */ 0 as NonSelfAnswers, 0 as NonSelfComments FROM Posts q INNER JOIN Posts p ON p.ParentId = q.Id AND p.OwnerUserId = q.OwnerUserId WHERE p.OwnerUserID = q.OwnerUserId AND q.Score >= 0 AND p.Score > 0 GROUP BY q.Id, q.OwnerUserId /* removes duplicates by collapsing the rows */ UNION /* to combine the two queries */ SELECT /* table for selecting comments from the post's owner */ q.Id as Id, q.OwnerUserId as OwnerUserId, 0 as SelfAnswers, /* ditto for matching columns */ Count(c.Id) as SelfComments, 0 as NonSelfAnswers, 0 as NonSelfComments FROM Posts q INNER JOIN Comments c ON c.PostId = q.Id AND c.UserId = q.OwnerUserId WHERE q.Score >= 0 AND q.PostTypeId = 1 /* we only want q to be question posts; if this is not how you imagined self-comments, edit! :) */ GROUP BY q.Id, q.OwnerUserId UNION SELECT /* table for selecting posts where the owner self-answered */ q.Id as Id, q.OwnerUserId as OwnerUserId, 0 as SelfAnswers, 0 as SelfComments, /* must have matching columns so 0 for comments column */ Count(p.Id) as NonSelfAnswers, 0 as NonSelfComments FROM Posts q INNER JOIN Posts p ON p.ParentId = q.Id AND p.OwnerUserId != q.OwnerUserId WHERE q.Score >= 0 AND p.Score > 0 GROUP BY q.Id, q.OwnerUserId /* removes duplicates by collapsing the rows */ UNION SELECT /* table for selecting comments from the post's owner */ q.Id as Id, q.OwnerUserId as OwnerUserId, 0 as SelfAnswers, /* ditto for matching columns */ 0 as SelfComments, 0 as NonSelfAnswers, Count(c.Id) as NonSelfComments FROM Posts q INNER JOIN Comments c ON c.PostId = q.Id AND c.UserId != q.OwnerUserId WHERE q.Score >= 0 AND q.PostTypeId = 1 /* we only want q to be question posts; if this is not how you imagined self-comments, edit! :) */ GROUP BY q.Id, q.OwnerUserId ) AS TableEntry GROUP BY TableEntry.Id, TableEntry.OwnerUserId /* removes duplicates by collapsing the rows and allows aggregation */ Order BY SelfAnswers DESC