Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SELECT statement
try WHERE status = 'Y' or status = NULL
if NULL doesn't work, try the empty character string ''
hope this helps,
Clint
"Mac Flores IV" <macflores_at_northwestern.edu> wrote in message
news:slrn9f0bk4.s0k.macflores_at_suzuki.wmitc.nwu.edu...
>
> 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 - 11:25:07 CDT