Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unanswered: multiple sum statements in same query

    Hi guys

    I want to do a query that sums up all the transactions < 0 and transactions > 0, in one query.
    So basically I will have

    select sum(amount1) as amount_won, sum(amount2) as amount_loss...

    in the same query, so amonut_won will be positive, while amount_loss will be negative. Is there a way to do this ?

    I am already able to do this by running the query twice (once for < 0 and another time for > 0). But this will produce 2 result sets, and this will be harder to manipulate.

    Thanks in advance !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sum(
             case when amount > 0 then amount else 0 end
              ) as positivesum
         , sum(
             case when amount < 0 then amount else 0 end
              ) as negativesum
      from ...
    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Posts
    21

    Thumbs up

    thanks a lot ! that worked great !

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314
    select sum1, sum2 from (select sum(amt) sum1 from X where amt >=0) T1, (select sum(amt) sum1 from X where amt < 0) T2;

Posting Permissions

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