I have the following SQL code:
SELECT COUNT(CASE WHEN upper(team) = 'TEAM ONE' THEN 1 END) TEAM_ONE,
COUNT(CASE WHEN upper(team) = 'TEAM TWO' THEN 1 END) TEAM_TWO
from TeamMembers WHERE UPPER(TEAM_NAME) = UPPER('My Team')
Team one has 8 members, team two has 2 members. The code returns:
Team One: 8
Team Two: 10
Apparently the count is being summed across both columns. Clearly I do not have a clear understanding on how the count function works yet all information I find online isn’t clear enough to me to solve this. Any one know what’s going on and how I can write this to return the count properly on each team?
5
This works just fine on my 19c database.
Which version are you using?
Sample data
with
TeamMembers(team_name, team) as (
select 'My Team', 'TEAM ONE' from dual
union all
select 'My Team', 'TEAM ONE' from dual
union all
select 'My Team', 'TEAM ONE' from dual
union all
select 'My Team', 'TEAM TWO' from dual
union all
select 'My Team', 'TEAM TWO' from dual
)
SELECT count(CASE WHEN upper(team) = 'TEAM ONE' THEN 1 END) TEAM_ONE,
count(CASE WHEN upper(team) = 'TEAM TWO' THEN 1 END) TEAM_TWO
from TeamMembers
WHERE UPPER(TEAM_NAME) = UPPER('My Team') ;
Possible solution (?)
Use sum() instead of count() since count() will also include null values
select * from (
SELECT count(1) as team_one_cnt
FROM TeamMembers
WHERE UPPER(TEAM_NAME) = UPPER('My Team')
AND upper(team) = 'TEAM ONE'
UNION
SELECT count(1) as team_two_cnt
FROM TeamMembers
WHERE UPPER(TEAM_NAME) = UPPER('My Team')
AND upper(team) = 'TEAM TWO' )
)