Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: Display all look up value groups even with zeros

    I have these values in a table:

    date serial_id auth_id
    10/1/04 1000 2
    10/2/04 1001 2
    10/2/04 1002 4
    10/3/04 1003 3
    10/3/04 1004 3
    10/4/04 1005 4
    10/4/04 1006 5
    10/4/04 1007 5

    Then there is another table, which has the description of auth_ids

    auth_id auth_desc
    1 In queue
    2 Viewing
    3 Working
    4 Checking I
    5 Processing
    6 Checking II
    7 Ordered

    I would like to count the serial_id for each dates for each auth_id with desc and also DISPLAY zeros for auth_ids that are not present.

    Is this possible. If so, can someone please tell me how I can accomplish this.

    Thanks,
    Saj

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may work:

    select m.auth_id, m.auth_desc, isnull(s.[date], '01/01/1900'), cnt=count(*)
    from auth_master m
    left outer join auth_serial s
    on m.auth_id = s.auth_id
    group by m.auth_id, m.auth_desc, isnull(s.[date], '01/01/1900')
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's call your first table with the data Table1, and the second table with the auth_ids Table2
    Code:
    select x.auth_id
         , x.auth_desc
         , x.date
         , count(y.serial_id) as serialcount
      from (
           select a.auth_id
                , a.auth_desc
                , d.date
             from Table2 a
           cross
             join (
                  select distinct date
                    from Table1
                  ) as d       
           ) as x
    left outer
      join Table1 as y
        on x.auth_id = y.auth_id
       and x.date    = y.date
    group
        by x.auth_id
         , x.auth_desc
         , x.date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I am surprised you decided to stop at the second subquery, Rudy
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have no idea what that means

    the SELECT DISTINCT DATE is clearly needed

    don't you agree?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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