Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004

    Question Unanswered: Query with count and result '0'

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


    Occupation_Code Name
    EL John
    PL Dick
    BD Charlse
    BD Fanny

    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?

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Query with count and result '0'

    Outer join the EMPLOYEE table to the OCCUPATION table.

  3. #3
    Join Date
    Mar 2004

    Re: Query with count and result '0'

    select occupation_code, o.occupation_name, count(
    from occupation o left outer join employee e using (occupation_code)
    group by occupation_code, occupation_name
    order by occupation_code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts