如何选择小于30的数字之和和大于30的数字之和?

发布于 2024-07-26 05:38:58 字数 114 浏览 6 评论 0原文

我正在尝试编写一个 SELECT 语句来选择字段的总和,但我想返回小于 30 的数字总和以及大于 30 的数字总和。我意识到我可以通过将两个选择连接在一起来做到这一点,但是我希望找到一种“巧妙”的方法来做到这一点。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

绝對不後悔。 2024-08-02 05:38:58

沿着这些思路(更正您的环境的语法):

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

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
善良天后 2024-08-02 05:38:58

这个要求是一个很大的模糊,但我会假设跨行求和并且<> 30. 这与案例答案不同。

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

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
吹泡泡o 2024-08-02 05:38:58

如果我理解正确,您有一个包含数值的列,并且您想要查找字段值小于 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

谢谢,
阿图尔

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文