Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Display sum, and defaulting to zero

    I have two tables that I am working with.

    Table 1 : column of departments.

    Table 2 : column of employees, column of department.

    I want to get a count of employees in each department.

    However, there will be departments that do not have employees and therefore are not listed in table 2, yet should be displayed as zero.

    So I need to query the two table so my results will look like this

    Administration 12
    HR 52
    Sciences 0

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    This is a very simple HOMEWORK assignment, try doing it on your own.

    HINT: Use outer join.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    I'm not sure outter join is the answer for me. I've over simplified my example b/c of the complexity of our data, but I think I over did it.

    What I think I need is a subquery.

    I have a query that goes against table one to get me the complete list of departments.

    I have a query that goes against table two and that gives me a count of employees in that department.

    I need to merge the two queries, knowing that if department from query 1 is not contained in query two, it should be displayed with a count of 0.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You do NOT need a sub-query, do NOT over complicate it.

    select a.dept_name,count(*) employees
    from emp b, dept a
    where a.dept_id = b.dept_id(+)
    group by a.dept_name;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713


    There you go, outer join (+) was the answer. Unfortunately the solution was given to you and you did not learn anything.

    There is a proverb that says: "Give a man a fish and he will eat one day, teach a man to fish and he will eat every day".



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your right, It is just frustrates me that so many sql developers, even people with experence use inline selects when a simple join will take care of it. I reacted instead of waiting. You are right and I was wrong.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by beilstwh
    Your right, It is just frustrates me that so many sql developers, even people with experence use inline selects when a simple join will take care of it.
    Excuse me but I fear I fall into the "experienced SQL developers that prefer inline selects to outer joins" . Why do inline selects irritate you that much ?

    I don't find them "complicated" and I find them very useful when you would need 2 or more outer joins to get the same result. You don't ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    by inline selects, I do NOT mean using a sub select in the from clause, I mean using a sub select in the select clause instead of a join.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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