How to SELECT sum of numbers less than 30 and sum of numbers greater than 30?

14,613

Solution 1

Something along these lines (correct the syntax for your environment):

SELECT
    sum(case when Field < 30 then Field else 0 end) as LessThan30,
    sum(case when Field > 30 then Field else 0 end) as MoreThan30
FROM
    DaTable

Solution 2

The requirement is a big vague, but I'll assume SUM across rows and <> 30. This is different to the case answer.

SELECT
    SelectCol1, SelectCol2, SelectCol3, ..., SUM(AggregateCol)
FROM
    Table
GROUP BY
    SelectCol1, SelectCol2, SelectCol3, ...
HAVING
    SUM(AggregateCol) <> 30

Solution 3

If i understand correctly, you have a single column which has numeric values and you want to find sum of this field for all thw rows where the field value is less than 30 and same condition with the variation of field value greater than 30. If this is correct, then you can use the below query and replcae the table and column name accordingly.

SELECT  SUM(CASE        WHEN col1 < 30 THEN COL1        ELSE 0 END
       )    SUM(CASE        WHEN col1 < 30 THEN COL1        ELSE 0 END
       ) FROM   DATABASE.SCHEMANAME.TABLE

Thanks, Atul

Share:
14,613
Admin
Author by

Admin

Updated on July 29, 2022

Comments

  • Admin
    Admin almost 2 years

    I'm trying to write a SELECT statement to select the sum of field but I want to return the sum of numbers less than 30 and also the sum of numbers greater than 30. I realize I can do this with two selects joined together, but I was hoping to find a "neat" way of doing it.

  • Lieven Keersmaekers
    Lieven Keersmaekers almost 15 years
    +1 Neat it is. If its performance you need, I doubt anything would beat tow selects joined together.
  • Vilx-
    Vilx- almost 15 years
    That's assuming you have an index on the column.
  • hansvb
    hansvb almost 15 years
    I do not think there is a performance problem with this query. Or to put it another way: there is no reason why two separate selects would be faster. And if you have an index on Field (and Field is not-null) this query can be resolved with just accessing that index.
  • Vilx-
    Vilx- almost 15 years
    That's assuming that the query optimizer is smart enough to do this. If not, this query will perform a full scan on the table. Two separate SELECT's with the condition in the WHERE part will always use the index, if it's available. At least that's what I think. I don't know which version of Oracle the OP is using, and how its query optimizer works.
  • Jeffrey Kemp
    Jeffrey Kemp almost 15 years
    I suspect Oracle does not go so far as to check whether the aggregate functions will return the same result whether rows with NULL are included or not; much more likely it will force a FTS unless you add WHERE Field IS NOT NULL, which is what I'd recommend here.
  • Jeffrey Kemp
    Jeffrey Kemp almost 15 years
    Yep - tested with 11g - Oracle will insist on a FTS unless you explicitly exclude the rows with NULL.
  • salman sigari
    salman sigari almost 15 years
    You could add a where clause: where Field is not null and Field != 30 May help if Field is 30 for most records and there's an index on Field.