Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    122

    Unanswered: SUM of a SUM that is > 0

    How can I get the SUM of a SELECT SUM that is > 0? Basically, I am calculating an account balance with a SELECT SUM statement and I want to go one step further. I would like a SUM of all account balances that are > 0. Thanks for any help.

  2. #2
    Join Date
    Jul 2004
    Posts
    60
    check out the 'HAVING' clause

    basic format:

    select groupby, sum(integer)
    from table
    group by groupby
    having sum(integer) > 0

    enjoy

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sum(acctbalance) as sumbalances
      from (
           select account
                , sum(balance) as acctbalance
             from yourtable
            group 
                by account
           having sum(balance) > 0
           ) as acctbalances
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Or you could be truly deviant, and use:
    Code:
    SELECT Sum(accountBalance)
       FROM myTable
       GROUP BY account
       HAVING 0 < Sum(accountBalance)
       COMPUTE Sum(Sum(accountBalance))
    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is truly deviant, because it returns a humungous number of unwanted rows

    mine returns only one, a single row with the desired answer

    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
  •