Q&A for professional and enthusiast programmers
WITH voteStats AS ( SELECT v.VoteTypeId, COUNT (*) AS totalVts, SUM ( CASE WHEN v.UserId is null THEN 0 ELSE 1 END ) AS haveUser, SUM ( CASE WHEN v.PostId is null THEN 0 ELSE 1 END ) AS havePost, SUM ( CASE WHEN v.CreationDate is null THEN 0 ELSE 1 END ) AS haveDate FROM Votes v GROUP BY v.VoteTypeId ) SELECT CASE WHEN (GROUPING (vt.Name) = 1) THEN 'GRAND TOTALS' ELSE ISNULL (vt.Name, 'UNKNOWN') END AS [Vote Type], FORMAT (SUM (vs.totalVts), '#,###') AS [Total], FORMAT (SUM (vs.haveUser), '#,###') AS [Have User], FORMAT (SUM (vs.havePost), '#,###') AS [Have Post], FORMAT (SUM (vs.haveDate), '#,###') AS [Have Date] FROM voteStats vs LEFT JOIN VoteTypes vt ON vs.VoteTypeId = vt.Id GROUP BY vt.Name WITH ROLLUP ORDER BY vt.Name