How to return the score and not count in the SQL query?

Question

If i have a table

AgentID | IsNew | TeamID 1 N 1 2 Y 2 3 Y 2 4 N 2 5 Y 1 

I want to return the following from the request:

 Team | CountIsNew = N | CountIsNew = Y 1 1 1 2 1 2 

Is there a way I can do this?
Using Oracle 10

Answer 1

 SELECT team, SUM(DECODE(IsNew, 'N', 1, 0)), SUM(DECODE(IsNew, 'Y', 1, 0)) FROM mytable GROUP BY team 

Answer 2

 SELECT TeamId , SUM(CASE WHEN IsNew = 'N' THEN 1 ELSE 0 END) AS CountIsNotNew , SUM(CASE WHEN IsNew = 'Y' THEN 1 ELSE 0 END) AS CountIsNew FROM Agent GROUP BY TeamId 

Answer 3

Another way: COUNT does not count NULL (except for COUNT(*) ):

 SELECT TeamId, COUNT(DECODE(IsNew,'N',1)) CountIsNotNew, COUNT(DECODE(IsNew,'Y',1)) CountIsNew FROM Agent GROUP BY TeamId; 

Or if you prefer CASE:

 SELECT TeamId, COUNT(CASE IsNew WHEN 'N' THEN 1 END) CountIsNotNew, COUNT(CASE IsNew WHEN 'Y' THEN 1 END) CountIsNew FROM Agent GROUP BY TeamId; 

(note: "1" can be any literal value)