如何将SCO_NET_VALUE与FRONT_END_NET_VALUE合并为总计表？

方案1：新增总计列（保留明细+展示合计） #

在原查询基础上新增总计字段，同时保留SCO和前端的明细数据，方便对比查看：

SELECT 
    storenumber AS 门店编号,
    bbbtendertypetext AS 支付类型,
    SUM(CASE WHEN TerminalNumber > 91 THEN (financialamounttendered - financialchangeamount) END) AS SCO净额,
    SUM(CASE WHEN TerminalNumber < 92 THEN (financialamounttendered - financialchangeamount) END) AS 前端净额,
    -- 合并计算两个终端类型的净额总计
    SUM(CASE WHEN TerminalNumber > 91 OR TerminalNumber < 92 THEN (financialamounttendered - financialchangeamount) END) AS 总计净额,
    COUNT(CASE WHEN TerminalNumber > 91 THEN transactionid END) AS SCO交易数,
    COUNT(CASE WHEN TerminalNumber < 92 THEN transactionid END) AS 前端交易数,
    -- 合并计算交易数总计
    COUNT(CASE WHEN TerminalNumber > 91 OR TerminalNumber < 92 THEN transactionid END) AS 总计交易数
FROM   
    [AceTLogData].[Tlog].[tender]
WHERE  
    transactiondatetime > '2022-09-22 00:00:00.000'
    AND transactiondatetime < '2022-09-23 00:03:00.000'
GROUP BY 
    storenumber, bbbtendertypetext
ORDER BY 
    storenumber

方案2：行转列合并（按终端类型分组+总计行） #

如果需要将SCO、前端、总计作为独立行展示，可通过CTE+UNION ALL实现：

-- 先统计SCO和前端的明细数据
WITH DetailData AS (
    SELECT 
        storenumber AS 门店编号,
        bbbtendertypetext AS 支付类型,
        CASE 
            WHEN TerminalNumber > 91 THEN 'SCO'
            WHEN TerminalNumber < 92 THEN '前端'
        END AS 终端类型,
        SUM(financialamounttendered - financialchangeamount) AS 净额,
        COUNT(transactionid) AS 交易数
    FROM   
        [AceTLogData].[Tlog].[tender]
    WHERE  
        transactiondatetime > '2022-09-22 00:00:00.000'
        AND transactiondatetime < '2022-09-23 00:03:00.000'
        AND (TerminalNumber > 91 OR TerminalNumber < 92) -- 过滤有效终端数据
    GROUP BY 
        storenumber, bbbtendertypetext,
        CASE 
            WHEN TerminalNumber > 91 THEN 'SCO'
            WHEN TerminalNumber < 92 THEN '前端'
        END
)
-- 合并明细记录与总计记录
SELECT 门店编号, 支付类型, 终端类型, 净额, 交易数
FROM DetailData
UNION ALL
SELECT 
    门店编号,
    支付类型,
    '总计' AS 终端类型,
    SUM(净额) AS 净额,
    SUM(交易数) AS 交易数
FROM DetailData
GROUP BY 门店编号, 支付类型
ORDER BY 门店编号, 支付类型, 终端类型

内容的提问来源于stack exchange，提问作者Artie