Help with summary counts 1

[mp9]

Hi,

I know this is probably simple, I'm just having one of those days today.

I have a table tblEvent, with three fields:

ID
Emp_ID
Status - takes either 1 or 0

I need to write a query that returns one row for each Emp_ID and then two separate count columns - one that counts how many times Status was 0 for that Emp_ID and one that returns how many times Status was 1.

So if my table had this in:
[tt]
ID Emp_ID Status
1 1 0
2 1 1
3 2 1
4 2 0
5 2 1
[/tt]
Then the query should return this:
[tt]
Emp_ID CountOfZero CountOfOne
1 1 1
2 1 2
[/tt]
Easy, hopefully? All help gratefully received.

 

[PHV]

SELECT Emp_ID
, SUM(CASE Status WHEN 0 THEN 1 ELSE 0 END) CountOfZero
, SUM(CASE Status WHEN 1 THEN 1 ELSE 0 END) CountOfOne
FROM tblEvent
GROUP BY Emp_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mp9]

Excellent, thank you.