Results 1 to 2 of 2

Thread: Group by Help!

  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: Group by Help!

    How do i get my group by to show null values as well?

    At the moment the following statement will discard the NULL rows:

    SELECT e.empno, e.fname, e.lname, w.location, w.buildingno, w.floorno,
    w.facilityno
    FROM employee e LEFT OUTER JOIN
    workarea w ON e.empno = w.empno
    GROUP BY w.location, w.buildingno, w.floorno, w.facilityno;

    Kindly regards,

    Nav
    Last edited by nbison; 09-11-03 at 07:34.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    write your query so that all non-aggregate columns in the SELECT list are in the GROUP BY

    with the GROUP BY that you have, the columns e.empno, e.fname, and e.lname should be involved in aggregate functions, e.g.
    Code:
    select count(distinct e.empno)
         , max(e.fname)
         , min(e.lname)
         , w.location, w.buildingno, w.floorno, w.facilityno
      from employee e 
    left outer
      join workarea w 
        on e.empno = w.empno
    group
        by w.location, w.buildingno, w.floorno, w.facilityno
    this query at least makes syntactic sense, although semantically it's pretty useless

    rudy
    http://r937.com/

Posting Permissions

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