CODEWITHSUNDEEP
sqloracle-databaseselect
Gribbler
Gribbler

Reputation: 434

How do I return count and not count in a SQL query?

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 a query:

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

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

Upvotes: 4

Views: 236

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Yet another way - COUNT doesn't count NULLs (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: the "1"s could be any literal value)

Upvotes: 0

Dirk
Dirk

Reputation: 31053

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

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425251

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

Upvotes: 8

Related Questions