Help: SELECT statement

GIVEN the following table:

TABLE Emp_status
Emp Status

A	Y
A	Y
B	Y
B	N
C	N
C	N
D	Y
D	N
E	Y
E	Y
F	N
F	N

I would like a SELECT statement which will count how many 'Y' each employee has. If there is no 'Y' then the employee gets a count of '0' as shown in the table VIEW below:

TABLE VIEW Emp_grade_vu
Emp Grade

A	2
B	1
C	0
D	1
E	2
F	0

I can do:
CREATE VIEW AS Emp_grade_vu AS
SELECT Emp, count(*) Status FROM Emp_status WHERE Status = 'Y'
ORDER BY Emp;

but this will obviously extract only those Emps with 'Y' status. How can I include the Emps with '0' Y values? Does this require a JOIN or do I have to structure some kind of DECODE function in the SELECT statement?

Any help is greatly appreciated. Received on Wed May 02 2001 - 10:57:56 CDT