0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

SELECT
    HasAnswer,
    State,
    COUNT(*) AS NumPosts,
    ROUND((100.0 * COUNT(*)) / SUM(COUNT(*)) OVER (PARTITION BY State), 2) AS PercentOfState,
    ROUND((100.0 * COUNT(*)) / SUM(COUNT(*)) OVER (), 2) AS PercentAll
FROM (
    SELECT
        Questions.Id,
        CASE 
            WHEN COUNT(Answers.Id) >= 1 THEN 'Yes'
            ELSE 'No'
        END AS HasAnswer,
        CASE
            WHEN Questions.DeletionDate IS NOT NULL THEN 'Deleted'
            WHEN Questions.ClosedDate IS NOT NULL THEN 'Closed'
            ELSE 'Open'
        END AS State
    FROM PostsWithDeleted AS Questions
    LEFT JOIN PostsWithDeleted AS Answers ON (
        Answers.ParentId = Questions.Id
        AND Answers.PostTypeId = 2
    )
    WHERE Questions.PostTypeId = 1
    GROUP BY Questions.Id, Questions.DeletionDate, Questions.ClosedDate
) AS QData
GROUP BY HasAnswer, State
ORDER BY HasAnswer, State

view current version

edited nov 17

created nov 17 jpmc2631240