T-SQL总和

Question

下面我有一个T-SQL查询，它返回购买的商品数量，减去折扣和总数-按购买的月/年分组。如何更新查询以返回总计行，以便将合计列中的金额相加？能够将所有行相加是很好的，但我的主要项目是我需要能够获得总计。谢谢。

    Select DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4)) 
    AS [Month],
    SUM(Amount) AS [Amount],
    SUM(Discount1) AS [Discount 1],
    SUM(Discount2) AS [Discount 2],
    SUM(Amount - Discount1 - Discount2) AS [Total]
    From 
    Orders
    JOIN Customer on orders.cust_ky=customer.cust_ky
    GROUP BY DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4))
    ORDER BY MAX(OrderDate)

Answer 1

根据您的sql-server版本，您可能能够实现rollup函数(SQL-Server 2005+)：

Select DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4)) AS [Month],
    SUM(Amount) AS [Amount],
    SUM(Discount1) AS [Discount 1],
    SUM(Discount2) AS [Discount 2],
    SUM(Amount - Discount1 - Discount2) AS [Total]
From Orders
JOIN Customer 
    on orders.cust_ky=customer.cust_ky
GROUP BY ROLLUP(DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4)))
ORDER BY MAX(OrderDate)

或者，您可以使用类似于下面的UNION ALL，其中第二个查询在没有GROUP BY的情况下获得总和

Select DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4)) AS [Month],
    SUM(Amount) AS [Amount],
    SUM(Discount1) AS [Discount 1],
    SUM(Discount2) AS [Discount 2],
    SUM(Amount - Discount1 - Discount2) AS [Total]
From Orders
JOIN Customer 
    on orders.cust_ky=customer.cust_ky
GROUP BY DATENAME(month, [OrderDate]) + ' ' + CAST(YEAR(OrderDate) AS CHAR(4))
union all
Select 'Total',
    SUM(Amount) AS [Amount],
    SUM(Discount1) AS [Discount 1],
    SUM(Discount2) AS [Discount 2],
    SUM(Amount - Discount1 - Discount2) AS [Total]
From Orders
JOIN Customer 
    on orders.cust_ky=customer.cust_ky