Subtracting grouped counts

[chribben]

Hello!
I have two COUNT-queries that I which to subtract. The first query is like:
SELECT winner, COUNT(winner)
FROM my_table
GROUP BY winner

The second is like:

SELECT contributor, COUNT(contribut or)
FROM my_table
GROUP BY contributor

The first query returns two columns with values (John, Jane);(2,3) and the second returns (John,Jane,Jack );(1,1,3). Now I want to subtract the first and the second so that the result is (John,Jane,Jack );(1,2,-3). How can this be accomplished? Any help much appreciated.

[Delerna]

Try this

SELECT a.contributor,isnull(b.cnt,0) - a.cnt as Cnt
FROM
(SELECT contributor, COUNT(contributor)  as Cnt
FROM my_table
GROUP BY contributor) a
left join
(SELECT winner, COUNT(winner) as Cnt
FROM my_table
GROUP BY winner)b on a.contributor=b.winner

[chribben]

Great! Thanks a lot.