SQL合并和选择一个结果

Question

我有两个针对同一个表的select查询，这两个查询在select部分中都只包含一个< code>sum()，但是在< code>where子句中有所不同。我需要做的是对两个查询的结果求和。

查询示例：

SELECT SUM(amount) 
FROM EXAMPLETABLE
WHERE  IDATE BETWEEN '2017/01/01' AND '2017/12/31' AND SOMESTATUS = 0

SELECT SUM(otherAmount) 
FROM EXAMPLETABLE
WHERE  IDATE BETWEEN '2018/01/01' AND '2018/12/31' AND SOMESTATUS = 1

我需要的是像这样的东西

SELECT SUM(amount+otherAmount)
FROM EXAMPLETABLE

其中选择金额和其他金额，如我的示例查询中所示。

我试着回答这个问题，但没能成功。我还尝试用子选择替换字段，但这不起作用，因为select不允许在聚合函数中使用

Answer 1

非常简单：

SELECT SUM((1-SOMESTATUS)*amount + SOMESTATUS*(otherAmount))
FROM EXAMPLETABLE
WHERE IDATE BETWEEN '2018-01-01' AND '2018-12-31'

…小心你的日期。我想你必须用破折号代替斜线

Answer 2

您可以进行这种条件聚合。

SELECT SUM(case when IDATE BETWEEN '2017/01/01' AND '2017/12/31' AND SOMESTATUS = 0 then amount else 0 end) +
SUM(case when IDATE BETWEEN '2018/01/01' AND '2018/12/31' AND SOMESTATUS = 1 then amount else 0 end) 
FROM EXAMPLETABLE

Answer 3

这是一个简单的方法:

SELECT SUM(b.a)
FROM (
    SELECT SUM(amount) a
    FROM EXAMPLETABLE
    WHERE IDATE BETWEEN '2017/01/01'
            AND '2017/12/31'
        AND SOMESTATUS = 0

    UNION ALL

    SELECT SUM(otherAmount) a
    FROM EXAMPLETABLE
    WHERE IDATE BETWEEN '2018/01/01'
            AND '2018/12/31'
        AND SOMESTATUS = 1
    ) AS b

我刚刚在两个查询之间创建了一个联合并对结果求和。