如何在SQL Server查询中添加Result=1时的WinSP求和及统计项

原可运行的查询语句：

SELECT
    RaceDateTime, Meeting, Horse, Jockey, Trainer, CardNumber,
    Result, [WinSP ], Going, Furlongs, Prize
FROM
    dbo.[RACE-TEMPLATE]
WHERE
    (1 = 1) AND
    (BetfairSP_Rank = 1) AND
    (YEAR(RaceDateTime) > 2016) AND
    (Furlongs <= 8) AND
    (Jockey = 'Smith')

需求：添加指定统计输出项，按以下标题输出结果：
COUNT (TOTAL RECORDS)、COUNT (Results = 1)、SUM ([WinSP ] of all cases where 'Result' = 1)

方案1：仅输出统计汇总行 #

如果只需要统计结果，不需要原明细数据，直接用聚合函数结合条件判断实现：

SELECT
    COUNT(*) AS [COUNT (TOTAL RECORDS)],
    SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS [COUNT (Results = 1)],
    SUM(CASE WHEN Result = 1 THEN [WinSP ] ELSE 0 END) AS [SUM ([WinSP ] of all cases where 'Result' = 1)]
FROM
    dbo.[RACE-TEMPLATE]
WHERE
    (1 = 1) AND
    (BetfairSP_Rank = 1) AND
    (YEAR(RaceDateTime) > 2016) AND
    (Furlongs <= 8) AND
    (Jockey = 'Smith')

• COUNT(*)直接统计过滤后的总记录数
• 用SUM(CASE...)统计Result=1的记录数（符合条件加1，否则加0）
• 第三个统计项通过CASE仅对Result=1的行累加[WinSP ]的值

方案2：保留原明细并显示统计值 #

如果需要保留原查询的所有明细数据，同时每行显示统计值，使用窗口函数：

SELECT
    RaceDateTime, Meeting, Horse, Jockey, Trainer, CardNumber,
    Result, [WinSP ], Going, Furlongs, Prize,
    COUNT(*) OVER() AS [COUNT (TOTAL RECORDS)],
    SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) OVER() AS [COUNT (Results = 1)],
    SUM(CASE WHEN Result = 1 THEN [WinSP ] ELSE 0 END) OVER() AS [SUM ([WinSP ] of all cases where 'Result' = 1)]
FROM
    dbo.[RACE-TEMPLATE]
WHERE
    (1 = 1) AND
    (BetfairSP_Rank = 1) AND
    (YEAR(RaceDateTime) > 2016) AND
    (Furlongs <= 8) AND
    (Jockey = 'Smith')

• OVER()子句基于整个过滤后的数据集计算统计值，每行都会显示相同的统计结果
• 同样通过CASE实现条件统计逻辑

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