Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Exclamation Unanswered: Return Null Record

    Can anyway help me? Currently, I'm using Ms Access
    The following is my data:

    <Position>
    -PositionID- -Position Description-
    1 Programmer
    2 System Analyst
    3 Software Developer

    <Employee>
    -EmpID- -EmpName- -PositionID-
    1 Amy 1
    2 John 1

    Where I using this query statement
    "SELECT Count(PositionID) as NoOfEmp,PositionID, FROM Employee"
    It return only:-
    -PositionID- -NoOfEmp-
    Programmer 2

    but is there anyway to show or display the following result:
    -PositionID- -NoOfEmp-
    Programmer 2
    System Analyst 0
    Software Developer 0

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select p.positionId, p.positionTitle, SUM(CASE WHEN e.positionId IS NOT NULL THEN 1 ELSE 0 END) As "Total"
    from position p
    LEFT OUTER JOIN
    employee e ON
    p.positionId = e.positionId
    GROUP BY p.positionId, p.positionTitle;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2004
    Posts
    7

    Smile Thank Guru

    Thanks man! your Statement are really work!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r123456, the CASE is not necessary, since all aggregate functions ignore nulls --
    PHP Code:
    select p.positionId
         
    p.positionTitle
         
    count(e.positionId) as "Total"
      
    from position p
    left outer 
      join employee e 
        on p
    .positionId e.positionId
    group 
        by p
    .positionId
         
    p.positionTitle 
    actually, there is one aggregate that does not ignore nulls, and that's count(*), which counts rows, not column values

    in this case count(e.positionId) will count only the non-null column values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16
    Originally posted by r123456
    select p.positionId, p.positionTitle, SUM(CASE WHEN e.positionId IS NOT NULL THEN 1 ELSE 0 END) As "Total"
    from position p
    LEFT OUTER JOIN
    employee e ON
    p.positionId = e.positionId
    GROUP BY p.positionId, p.positionTitle;

    It's interesting how the aggregate function works.
    I would think that ...count(e.positionId) would
    create a (temporary) table with only 1 records:

    Programmer 2

    since in this instance Count() is concerned only with
    the employee table and not the positions table,
    and is not even aware of any position except "1",
    and then the outer join with all the positions would
    result in

    Programmer 2
    Sys Analyst NULL
    Soft Dev NULL

    In other words, logically it seems the CASE statement
    suggested by you would be necessary, but yet
    it isn't as mentioned by r937. Funny!

Posting Permissions

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