Good, bad, and indifferent percentages by post type

0

Please login or register to vote for this query.

(click on this box to dismiss)

Good posts have Score > 0, Bad posts have Score < 0, and Indifferent have Score = 0.

Stack Overflow

Q&A for programmers

with types as (
    SELECT
        Posts.PostTypeId,
        COUNT(*) as Total,
        SUM(CASE when Posts.Score > 0 then 1 else 0 END) as PositiveScore,
        SUM(CASE when Posts.Score < 0 then 1 else 0 END) as NegativeScore
    FROM Posts
    LEFT JOIN Posts as Parent on Posts.ParentId=Parent.Id
    WHERE Posts.PostTypeId in (1,2) AND Parent.ClosedDate is null
    GROUP BY Posts.PostTypeId
)

select
    PostTypes.Name as PostType,
    Total as Posts,
    CAST(convert(numeric(5,2), cast(PositiveScore as float) * 100 / Total) as VARCHAR(10)) + '%' as Good,
    CAST(convert(numeric(5,2), cast(NegativeScore as float) * 100 / Total) as VARCHAR(10)) + '%' as Bad,
    CAST(convert(numeric(5,2), cast((Total-PositiveScore-NegativeScore) as float) * 100 / Total) as VARCHAR(10)) + '%' as Indifferent
from types
LEFT JOIN PostTypes on PostTypes.Id=types.PostTypeId
order by PostTypes.Name DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached