Results 1 to 2 of 2

Thread: group by query

  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unanswered: group by query

    Hi

    I have the following query:

    select tbl.id, nvl(sum(x),0) as A, nvl(count(y),0) as B from .... where tbl.id in (1,2,3) group by tbl.id

    And here are the results I am currently seeing:

    tbl.id A B
    1 232 343
    3 3434 343

    The table where tbl.id=2 has 0 for both columns so it does not show up.
    How can I modify the query so that I will get a result set as the following:

    tbl.id A B
    1 232 343
    2 0 0
    3 3434 343

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > "The table where tbl.id=2 has 0 for both columns so it does not show up"

    i'm having trouble believing this

    your query must return a row for the 2 group, regardless of whether the 2 row(s) have 0 in the x and/or y columns, or nulls, or anything else

    if at least one row for 2 exists, there will be a 2 group in the results, unless it's eliminated by a HAVING clause

    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
  •