By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,474 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

Subtracting grouped counts

P: 14
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(contributor)
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.
Feb 10 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,134
Try this

Expand|Select|Wrap|Line Numbers
  1. SELECT a.contributor,isnull(b.cnt,0) - a.cnt as Cnt
  2. FROM
  3. (SELECT contributor, COUNT(contributor)  as Cnt
  4. FROM my_table
  5. GROUP BY contributor) a
  6. left join
  7. (SELECT winner, COUNT(winner) as Cnt
  8. FROM my_table
  9. GROUP BY winner)b on a.contributor=b.winner
Feb 11 '08 #2

P: 14
Great! Thanks a lot.
Feb 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.