如何用SQL将Fact_Transactions表中两类交易的Amount_Eur总和相加？

如果你只需要这两种交易类型的总金额，最简单的方式是直接去掉分组，对符合条件的Amount_Eur直接求和：

SELECT
    SUM(Amount_Eur) AS Total_Total_Ins
FROM
    fact_transactions
WHERE
    type_trans IN ('CARD-INCOMING', 'BANK-INCOMING');

如果需要同时保留原来的分组统计结果和总金额，可以用ROLLUP扩展分组查询：

SELECT
    COALESCE(type_trans, '总合计') AS type_trans,
    SUM(Amount_Eur) AS Total_Ins
FROM
    fact_transactions
WHERE
    type_trans IN ('CARD-INCOMING', 'BANK-INCOMING')
GROUP BY
    ROLLUP(type_trans);

这个查询会返回三条结果：两条各类型的单独统计，一条标记为总合计的总金额。

另外，也可以基于原查询的结果做二次求和，有两种常用写法：

子查询方式 #

SELECT
    SUM(Total_Ins) AS Total_Total_Ins
FROM (
    SELECT
        SUM(Amount_Eur) AS Total_Ins
    FROM
        fact_transactions
    WHERE
        type_trans IN ('CARD-INCOMING', 'BANK-INCOMING')
    GROUP BY
        type_trans
) AS subquery;

CTE（公共表表达式）方式 #

WITH type_totals AS (
    SELECT
        SUM(Amount_Eur) AS Total_Ins
    FROM
        fact_transactions
    WHERE
        type_trans IN ('CARD-INCOMING', 'BANK-INCOMING')
    GROUP BY
        type_trans
)
SELECT
    SUM(Total_Ins) AS Total_Total_Ins
FROM type_totals;

内容的提问来源于stack exchange，提问作者Mr.B