case when (COUNT(team_Name) = (SUM(status = 'Completed')) then 'Complete'
case when (COUNT(team_Name) = (SUM(status = 'On Hold')) then 'On hold'
case when (COUNT(team_Name) = (SUM(status = 'Pending')) then 'Pending'
else 'In Progress' end

case COUNT(team_Name)
    when SUM(CASE WHEN status = 'Completed' THEN 1 END) then 'Complete'
    when SUM(CASE WHEN status = 'On Hold' THEN 1 END)   then 'On Hold'
    when SUM(CASE WHEN status = 'Pending' THEN 1 END)   then 'Pending'
       else 'Abandoned' end

Select YourFieldAliasName =
CASE TEAM_ID
    WHEN 'Completed' THEN 'Complete'
WHEN 'On Hold' THEN 'Complete'
WHEN 'Pending' THEN 'Pending'
    ELSE 'Abandoned'
END