1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

created aug 30 15 Brock Adams