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.
views:
598answers:
3
+17
A:
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
Vilx-
2009-07-03 07:46:17
+1 Neat it is. If its performance you need, I doubt anything would beat tow selects joined together.
Lieven
2009-07-03 08:05:33
That's assuming you have an index on the column.
Vilx-
2009-07-03 10:39:00
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.
Thilo
2009-07-03 10:47:09
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.
Vilx-
2009-07-03 11:46:43
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
2009-07-03 14:23:11
Yep - tested with 11g - Oracle will insist on a FTS unless you explicitly exclude the rows with NULL.
Jeffrey Kemp
2009-07-03 14:26:38
You could add a where clause: where Field is not null and Field != 30May help if Field is 30 for most records and there's an index on Field.
Pop
2009-07-03 15:14:18
A:
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
Atul
2009-07-03 07:53:01
A:
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
gbn
2009-07-03 11:06:18