views:

1532

answers:

9

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

Edit: please read carefully, I want to do it IN 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(Poisition = Other)) so WHERE is no use for me in this example

A: 

Simply add a WHERE clause and you're good to go.

Kyle Rozendo
Downvoting after a question is edited to clarify is just silly. Especially singular downvotes to the same answer multiple times.
Kyle Rozendo
A: 
SELECT COUNT(*) FROM table WHERE column = 'Manager'
Lukasz Lysik
"IN Count() statement". i don't need where
agnieszka
You can write your own CLR aggregate function. Maybe that would do.
Lukasz Lysik
A: 
SELECT COUNT(*) FROM bla WHERE Position = 'Manager'
Petoj
please read edit, it's not the answer
agnieszka
A: 

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

NawaMan
Why do I get a down vote? After I answered (or may be the same time), many people answered the similar thing and they do not get any downvote. /:(
NawaMan
+1: Agreed, have one from me...
RedFilter
A: 

Do you mean just this:

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

If so, then yup that works!

Dana
+16  A: 

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 ...
RedFilter
Why the downvotes? A comment would be helpful.
RedFilter
upvote from me ;) thanks
agnieszka
Unbelievable! This was *the first correct answer* to be posted and it's been downvoted several times.
LukeH
+1 for you as the downvote is not justified.
NawaMan
@RedFilter It's not even necessary to specify the `else` part, just `end` it right after the `1`.
Denis Valeev
@Denis: correct - I often leave the `else` in as it better documents the results of the case statement, especially for novie SQL developers. For brevity, it can be removed in this case.
RedFilter
+4  A: 

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
AdaTheDev
+15  A: 

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 ...
Guffa
+3  A: 

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')
Matthew Whited