Bounty Award Counts

2

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the number of bounties awarded at each bounty level (-1 = bounty expired, 0 = self-accepted)

English

Q&A for linguists, etymologists, and serious English language enthusiasts

-- Bounty Award Counts
-- Shows the number of bounties awarded at each bounty level (-1 = bounty expired, 0 = self-accepted)

WITH BountyAmounts AS
(
    SELECT DISTINCT BountyAmount
        FROM Votes
        WHERE
            VoteTypeId = 9 AND
            BountyAmount IS NOT NULL
),
Results AS
(
    SELECT
        b.BountyAmount,
        (
            SELECT COUNT(*)
                FROM Votes v
                WHERE
                    v.VoteTypeId = 9 AND
                    v.BountyAmount = b.BountyAmount
        ) AS Count
        FROM BountyAmounts b

    UNION ALL

    SELECT
        -1 AS BountyAmount,
        (
            SELECT COUNT(*)
                FROM Votes
                WHERE
                    VoteTypeId = 9 AND BountyAmount IS NULL
        ) AS Count
)
SELECT
    CASE WHEN GROUPING(BountyAmount) > 0 THEN NULL ELSE BountyAmount END AS BountyAmount,
    SUM(Count) AS Count
    FROM Results
    GROUP BY BountyAmount WITH ROLLUP
    ORDER BY
        CASE WHEN BountyAmount IS NULL THEN 1 ELSE 0 END,
        BountyAmount

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results