0

Please login or register to vote for this query.

(click on this box to dismiss)

compare performance of question tagged 'ant java' with those just tagged 'ant'

Q&A for Ubuntu users and developers

-- Tag smackdown: java-ant versus ant
-- compare performance of question tagged 'ant java' with those just tagged 'ant'

SELECT 
    CASE 
    WHEN ptj.PostId IS NULL
    THEN
         'ant'
    ELSE 'ant and java'
    END,
    COUNT( DISTINCT pta1.PostId ),
    SUM( p.Score ),
    SUM( ISNULL( p.AnswerCount, 0 ) ),
    SUM( CASE WHEN p.AcceptedAnswerId IS NULL THEN 0 ELSE 1 END )
FROM
    PostTags pta1

LEFT JOIN
   PostTags ptj
ON    ptj.PostId = pta1.PostId
AND   ptj.TagId = ( SELECT id FROM Tags where TagName = 'java' )

LEFT JOIN
   Posts p
ON p.Id = pta1.PostId

WHERE pta1.PostId IN (
SELECT
        pta.PostId
FROM
      PostTags pta

INNER JOIN
      Tags ta
ON    ta.Id = pta.TagId
AND   ta.TagName = 'ant'
)
GROUP BY
    CASE 
    WHEN ptj.PostId IS NULL
    THEN
         'ant'
    ELSE 'ant and java'
    END

fork query

created feb 8 11 martin clayton