Re: Help: SELECT statement

instead of count(*) try Sum(Decode(status, 'Y', 1, 'y', 1, 0)) this should count 0's for N's which if you have 2 N's you would get the sum of 2 0's which is 0. I haven't tested this but something along these lines whould work and if this doesn't exactly it shouldn't be hard to modify.

Mac Flores IV wrote in message ...
>
>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 - 13:51:06 CDT