Results 1 to 8 of 8

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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Post

    If a row for tbl.id=2 exists, then do this:

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

    If otherwise now row exists for tbl.id=2, then you need to auto-generate this row (there are many ways to do this depending on the characteristics of tbl.id).

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

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    use this query...

    select id,sum(nvl(x,0)) A, sum(nvl(y,0) B from tbl
    group by id
    I checked it works..

    --Jaggu

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

    Exclamation

    Originally posted by jaggu
    use this query...

    select id,sum(nvl(x,0)) A, sum(nvl(y,0) B from tbl
    group by id
    I checked it works..

    --Jaggu
    Jaggu:

    sum(nvl(y,0) B is actually a COUNT(), therefore the:

    sum(decode(nvl(y,0),0,0,1) as B !!!

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

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    any way I am getting the same result, is there any performance issued...

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

    Exclamation

    Originally posted by jaggu
    any way I am getting the same result, is there any performance issued...
    SUM(X) is NEVER = COUNT(X)!!!! (unless all X=(1 or 0 or null)).



    SQL>select sum(1), count(0) from all_objects where rownum <11;

    SUM(1) COUNT(0)
    ---------- ----------
    10 10

    1 row selected.

    SQL> select sum(2), count(2) from all_objects where rownum <11;

    SUM(2) COUNT(2)
    ---------- ----------
    20 10

    1 row selected.

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

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    I am sorry, I realy do not know how
    sum(nvl(y,0) B is actually a COUNT(),

    I would appreciate if you could give me more detail..

    --Jaggu

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

    Exclamation Re: group by query

    Look at the original post:

    Originally posted by mojojo82
    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
    Notice the nvl(count(y),0) ?

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

Posting Permissions

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