Is it possible to specify condition in Count()?

Question

Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column.

I want to do it in the count statement, not using WHERE; I'm asking about it because I need to count both Managers and Other in the same SELECT (something like Count(Position = Manager), Count(Position = Other)) so WHERE is no use for me in this example.

Answer 1

I think you can use a simple WHERE clause to select only the count some record.

Answer 2

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

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

Answer 3

You can also use the Pivot Keyword if you are using SQL 2005 or above

more info and from Technet

SELECT *
FROM @Users
PIVOT (
    COUNT(Position)
    FOR Position
    IN (Manager, CEO, Employee)
) as p

Test Data Set

DECLARE @Users TABLE (Position VARCHAR(10))
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('CEO')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')

Answer 4

I know this is really old, but I like the NULLIF trick for such scenarios, and I found no downsides so far. Just see my copy&pasteable example, which is not very practical though, but demonstrates how to use it.

NULLIF might give you a small negative impact on performance, but I guess it should still be faster than subqueries.

DECLARE @tbl TABLE ( id [int] NOT NULL, field [varchar](50) NOT NULL)

INSERT INTO @tbl (id, field)
SELECT 1, 'Manager'
UNION SELECT 2, 'Manager'
UNION SELECT 3, 'Customer'
UNION SELECT 4, 'Boss'
UNION SELECT 5, 'Intern'
UNION SELECT 6, 'Customer'
UNION SELECT 7, 'Customer'
UNION SELECT 8, 'Wife'
UNION SELECT 9, 'Son'

SELECT * FROM @tbl

SELECT 
    COUNT(1) AS [total]
    ,COUNT(1) - COUNT(NULLIF([field], 'Manager')) AS [Managers]
    ,COUNT(NULLIF([field], 'Manager')) AS [NotManagers]
    ,(COUNT(1) - COUNT(NULLIF([field], 'Wife'))) + (COUNT(1) - COUNT(NULLIF([field], 'Son'))) AS [Family]
FROM @tbl

Comments appreciated :-)

Answer 5

SELECT COUNT(*) FROM bla WHERE Position = 'Manager'

Answer 6

Do you mean just this:

SELECT Count(*) FROM YourTable WHERE Position = 'Manager'

If so, then yup that works!