oracle 如何选择小于30的数字总和和大于30的数字总和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1078240/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to SELECT sum of numbers less than 30 and sum of numbers greater than 30?
提问by Vilx-
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.
我正在尝试编写一个 SELECT 语句来选择字段的总和，但我想返回小于 30 的数字总和以及大于 30 的数字总和。我意识到我可以通过将两个选择连接在一起来做到这一点，但是我希望找到一种“整洁”的方式来做到这一点。
回答by Vilx-
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
回答by Atul
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.
如果我理解正确，您有一个包含数值的列，并且您想为字段值小于 30 且条件相同且字段值变化大于 30 的所有行找到该字段的总和。如果这是正确的，那么您可以使用以下查询并相应地重新计算表和列名称。
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
谢谢，阿图尔
回答by gbn
The requirement is a big vague, but I'll assume SUM across rows and <> 30. This is different to the case answer.
该要求非常模糊，但我将假设跨行和 <> 30 的 SUM。这与案例答案不同。
SELECT
SelectCol1, SelectCol2, SelectCol3, ..., SUM(AggregateCol)
FROM
Table
GROUP BY
SelectCol1, SelectCol2, SelectCol3, ...
HAVING
SUM(AggregateCol) <> 30
