Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs
-- Based off of this query: DECLARE @userId int = ##UserId## DECLARE @searchUserId1 nvarchar(25) = '%"Id":' + CAST(@userId AS nvarchar(MAX)) + '}' DECLARE @searchUserId2 nvarchar(25) = '%"Id":' + CAST(@userId AS nvarchar(MAX)) + ',%' DECLARE @closeReasons table ( Id int NOT NULL PRIMARY KEY, Description nvarchar(200) NOT NULL ) INSERT INTO @closeReasons VALUES (1, 'Exact duplicate'), (2, 'Off topic'), (3, 'Subjective and argumentative'), (4, 'Not a real question'), (7, 'Too localized'), (20, 'Noise or pointless'), (102, 'off-topic'), (103, 'unclear what youre asking'), (104, 'too broad'), (105, 'primarily opinion-based'), (201, 'off-topic (channels)') --select top 10 * from PostHistory SELECT -- ( -- CASE WHEN GROUPING(Description) = 1 THEN -- '(Total)' -- ELSE -- (CASE WHEN Description IS NULL THEN '(Unknown)' ELSE Description END) -- END -- ) AS 'Close Reason', -- SUM(Count) AS Count Description AS 'Close Reason', CloseReasonId, Count FROM ( SELECT CAST(PostHistory.Comment AS int) AS CloseReasonId, 1 AS Count FROM PostHistory WHERE (PostHistoryTypeId = 10) AND ((Text LIKE @searchUserId1) OR (Text LIKE @searchUserId2)) ) a LEFT OUTER JOIN @closeReasons cr ON cr.Id = a.CloseReasonId -- GROUP BY Description WITH ROLLUP -- ORDER BY -- GROUPING(Description), -- SUM(Count) DESC