SQL查询求助：如何将77行数据的ValueInDefaultPriceUnit求和为单一revenue？

你当前的查询用了sum(ValueInDefaultPriceUnit) over (partition by IntId)，这会按IntId分组计算每组的总和，所以每行都会重复显示对应分组的求和结果，而不是全局的单一总和。根据你的需求，分两种情况处理：

情况1：只返回单一的revenue总和（合并所有77行为一行） #

直接使用聚合函数SUM，去掉不需要的非聚合列：

SELECT 
    SUM(ValueInDefaultPriceUnit) AS revenue
FROM _EPOS_DocumentPayments AS dp 
INNER JOIN _EPOS_Documents AS d ON d.ID = dp.ID
WHERE BalanceId BETWEEN 62 AND 64
AND StateId BETWEEN 2 AND 4
AND DocumentTypeId = 4
AND DepartmentId = 4

情况2：保留原有列，但每行的revenue都是所有行的总和 #

去掉窗口函数里的partition by IntId，让求和范围覆盖所有符合条件的行：

SELECT 
    IntId,
    ValueInDefaultPriceUnit,
    Test=0,
    DocumentTypeId,
    Enabled=1,
    DepartmentId,
    BalanceId,
    Date,
    StateId,
    SUM(ValueInDefaultPriceUnit) OVER () AS revenue
FROM _EPOS_DocumentPayments AS dp 
INNER JOIN _EPOS_Documents AS d ON d.ID = dp.ID
WHERE BalanceId BETWEEN 62 AND 64
AND StateId BETWEEN 2 AND 4
AND DocumentTypeId = 4
AND DepartmentId = 4

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