How to SELECT sum of numbers less than 30 and sum of numbers greater than 30?
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
Admin
Updated on July 29, 2022Comments
-
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 almost 15 years+1 Neat it is. If its performance you need, I doubt anything would beat tow selects joined together.
-
Vilx- almost 15 yearsThat's assuming you have an index on the column.
-
hansvb almost 15 yearsI 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- almost 15 yearsThat'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 almost 15 yearsI 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 almost 15 yearsYep - tested with 11g - Oracle will insist on a FTS unless you explicitly exclude the rows with NULL.
-
salman sigari almost 15 yearsYou 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.