I have the following PL/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?