DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Check condition based on group by query!!!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Check condition based on group by query!!!

    Hi,

    I have a query that returns sum amount for a specific transaction.

    select drvr_num, sum(amount)
    from ledger
    where transaction_type = 'CLMR'
    group by oracle_drvr_num

    Result set:

    Code:
    DRVR_NUM SUM(AMOUNT)
    ------- -----------
    221751       -2000
    221752       -2000
    221753       -1400
    221754           0
    221755           0
    221756           0
    221757           0
    221758       -2000
    221759           0
    221761           0
    221762           0
    The thing is I don't want to display the rows with 0 amount. I have a table with mulitple record for each driver with positive and negative amount. So when I sum it, it gives me the sum amount with zeroes as well, for example they may have -50 and +50, it comes out to be zero and I don't want those rows. Is there a way to do this in a subquery. I have to create a report for it in Reports 6i and I tried using the formula but it returns each row multiple times. I thought there might be a solution if i try writing a subquery. Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Add the line:
    having sum(amount) <> 0
    as the last line (and correct the typo).

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    yeah man, i just tried that as I was reading the book. Thanks for helping me.

Posting Permissions

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


Click Here to Expand Forum to Full Width