Is it possible to specify condition in Count()?

Solution 1:

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

Solution 2:

Assuming you do not want to restrict the rows that are returned because you are aggregating other values as well, you can do it like this:

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount
from ...

Let's say within the same column you had values of Manager, Supervisor, and Team Lead, you could get the counts of each like this:

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount,
    count(case when Position = 'Supervisor' then 1 else null end) as SupervisorCount,
    count(case when Position = 'Team Lead' then 1 else null end) as TeamLeadCount,
from ...

Solution 3:

@Guffa 's answer is excellent, just point out that maybe is cleaner with an IF statement

select count(IIF(Position = 'Manager', 1, NULL)) as ManagerCount
from ...

Solution 4:

Depends what you mean, but the other interpretation of the meaning is where you want to count rows with a certain value, but don't want to restrict the SELECT to JUST those rows...

You'd do it using SUM() with a clause in, like this instead of using COUNT(): e.g.

SELECT SUM(CASE WHEN Position = 'Manager' THEN 1 ELSE 0 END) AS ManagerCount,
    SUM(CASE WHEN Position = 'CEO' THEN 1 ELSE 0 END) AS CEOCount
FROM SomeTable