Count query where count(*) is 0 ???

[mozart]

I have basically two tables as below and wish to do a count


EMPLOYEE

Occupation_Code Name
-----------------------------
EL John
PL Dick
BD Charlse
BD Fanny


OCCUPATION
Occupation_Code Occupation_Name

EL Electrician
Pl Plumber
BD Builder
CL Cleaner
CK Cook


Now i need to do a count of how many people I have and group them by Occupation_Code for every single Occupation code that exists in OCCUPATION.

Now if i do a simple count on EMPLOYYE then i will only get a result for occupation_code that exists in this table

ie Occupation_Code Count(*)
EL 1
PL 1
BD 2


What i want is also the CL and CK occupations code and i want them to read 0

So i want this result

Occupation_Code Count(*)
EL 1
PL 1
BD 2
CK 0
CL 0



Any tips on how to get this query out?

Thankyou

[DaPi]

Try something along these lines (untested):

select o.Occupation_Code, sum(e.sums)
from   occupation o,
(select Occupation_Code, count(*) sums
 from   employee
 group by Occupation_Code) e
where  o.Occupation_Code = e.Occupation_Code(+)
group by o.Occupation_Code

[oracle_faq]

Try this also.

select o.occupation_code, count(*)
from occupation o, employee e
where o.occupation_code = e.occupation_code(+)
group by o.occupation_code

[DaPi]

Originally posted by oracle_faq
select o.occupation_code, count(*)
from occupation o, employee e
where o.occupation_code = e.occupation_code(+)
group by o.occupation_code

I think that will give a count of 1 for Occupations with no Employees.
count(employee_name) should work if there are no rows where employee_name is NULL.

P.S. better: count(e.occupation_code) !

[oracle_faq]

Agree with DaPi....didn't run the query to see the results in the forst place.

or something like that

select o.occupation_code,
sum(decode(e.occupation_code, null, 0, 1))....

[slimdave]

Or ...

select o.occupation_code, count(e.occupation_code)
from occupation o, employee e
where o.occupation_code = e.occupation_code(+)
group by o.occupation_code