如何在现有SQL查询中按Week Of分组并对Count列求和

是的，子查询是实现该需求的可行方式，不过也可以通过简化原查询逻辑来更高效地得到结果，以下是两种实现方式：

方法一：使用子查询（基于现有查询结果聚合） #

将你现有的查询作为子查询，外层按Owner和Week Of分组，对Count列求和即可：

DECLARE @interval as INT = 30;
DECLARE @StartDate as datetime = '2023-01-01 00:00:00';

SELECT
    Owner,
    [Week Of],
    SUM([Count]) AS Sum
FROM (
    -- 原查询逻辑，保留间隔分组用于统计各时段数量
    SELECT
        owneridname [Owner],
        CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week Of],
        CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @StartDate, createdon) / CAST(@interval as decimal)) * @interval, @StartDate) as TIME(0)) [createdon_datetime_30_min_interval],
        COUNT(*) [Count]
    FROM incident
    WHERE 
        createdon > DATEADD(day, -5, GETDATE()) 
    GROUP BY
        CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @StartDate, createdon) / CAST(@interval as decimal)) * @interval, @StartDate) as TIME(0)),
        owneridname,
        CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date)
) AS IntervalCounts
GROUP BY Owner, [Week Of]
ORDER BY Owner ASC, [Week Of] ASC;

方法二：直接简化原查询（更高效） #

由于你最终只需要每个用户每周的总数量，中间的30分钟间隔分组其实是多余的，可以直接跳过该层聚合，直接按Owner和Week Of分组统计总数：

DECLARE @StartDate as datetime = '2023-01-01 00:00:00';

SELECT
    owneridname [Owner],
    CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week Of],
    COUNT(*) AS Sum
FROM incident
WHERE 
    createdon > DATEADD(day, -5, GETDATE()) 
GROUP BY
    owneridname,
    CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date)
ORDER BY Owner ASC, [Week Of] ASC;

两种方法都能得到你期望的结果，方法二减少了一次聚合操作，性能更优。

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