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

    Question Unanswered: Query with count and result '0'

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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
    Posts
    23

    Re: Query with count and result '0'

    select occupation_code, o.occupation_name, count(e.name)
    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
  •