SQL Server查询：获得有条件总和 [英] SQL Server query : get the sum conditionally

问题描述

由于数据

我需要通过包括拿出这个 SalesNew 列：

I need to come up to this by including SalesNew column:

SalesNew 列将计算每个项目的销售额基础上有条件的组号的总和。

SalesNew column will compute for the sum of each items sales based on their group number conditionally.

根据上表为例。当项目组号为1，将刚才复制或保留其销售。当它不是1时，SalesNew柱应该得到每组的所有项的总和，如在给定的例子中，salesNew显示20为第3项和第4项，因为它增加了他们的销售，这两个具有10所以项目3和4 salesNew值20。

Example based on the table above. when the group number of item is 1, IT WILL JUST COPY OR RETAIN ITS SALES. When it is not 1, SalesNew column should get the sum of all items per group, like in the given example, salesNew displays 20 for item 3 and item 4 because it adds their sales that both having 10. So item 3 and 4 salesNew value is 20.

我知道的总和函数，但这不显示所需的输出。我希望有人可以帮助我走出这一点。

I know the sum function but this doesn't display the desired output. I hope anyone could help me out on this.

在此先感谢

推荐答案

您可以使用 SUM（销售部）OVER（PARTITION BY [组]），以组​​合 CASE ：

You could use SUM(Sales) OVER(PARTITION BY [Group]), with a combination of CASE:

SELECT *,
    salesNew = 
        CASE
            WHEN [Group] = 1 THEN Sales
            ELSE SUM(Sales) OVER(PARTITION BY [Group])
        END
FROM Data

SQL小提琴

您也可以使用 CROSS APPLY ：

SELECT
    d.*, 
    salesNew = 
        CASE
            WHEN [Group] = 1 THEN Sales
            ELSE x.salesNew
        END
FROM Data d
CROSS APPLY(
    SELECT salesNew = SUM(Sales)
    FROM Data
    WHERE [Group] = d.[Group]
)x

SQL小提琴

这篇关于SQL Server查询：获得有条件总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！