sql - SUM GROUP BY 给出不想要的结果

Question

T1 表的前 12 行:

Name    Status  Duration
Todd    Active  60
Todd    Active  60
Todd    Active  60
Todd    Schedu  60
Todd    Schedu  60
Todd    Schedu  120
Todd    Schedu  120
Bran    Active  30
Bran    Active  30
Bran    Active  60
Bran    No Show 120
Bran    No Show 120

如果我运行此查询(或使用不带 GROUP BY 的 DISTINCT):

SELECT Name, Status, Duration 
FROM Table T1
GROUP BY Name,Status,Duration

我明白了:

Name    Status  Duration
Todd    Active  60
Todd    Schedu  60
Todd    Schedu  120
Bran    Active  30
Bran    Active  60
Bran    No Show 120

根据上述结果，我希望 desired result 为 SUM(Duration) GROUPED BY Name, Status:

Name    Status  Duration
Todd    Active  60
Todd    Schedu  180
Bran    Active  90
Bran    No Show 120

我正在尝试这个查询以达到预期的结果:

SELECT Name, Status, SUM(Duration)
FROM Table T1
GROUP BY Name,Status

但是我得到了 SUM(Duration) 的大量数字 - 它可能是添加所有持续时间，而不是每组名称和状态的不同持续时间。

Answer 1

获得所需内容的一种方法是使用子查询:

SELECT Name, Status, SUM(Duration)
FROM (SELECT Name, Status, Duration 
      FROM Table T1
      GROUP BY Name,Status,Duration
     ) NSD
GROUP BY Name, Status;