SQL SUM() 函数忽略 WHERE 子句和 CASE 语句

Question

    SELECT      u.FirstName + ' ' + u.LastName as 'User',
            COUNT(*) as 'Number of Calls',
            CONVERT(varchar(4), SUM(CASE WHEN c.FromTime BETWEEN @FromDate AND @ToDate
                THEN DATEDIFF(mi, c.FromTime, c.ToTime) ELSE 0 END) / 60) + ':' + 
            CASE WHEN SUM(CASE WHEN c.FromTime BETWEEN @FromDate AND @ToDate
                THEN DATEDIFF(mi, c.FromTime, c.ToTime) ELSE 0 END) % 60 < 10 THEN '0' ELSE '' END +
            CONVERT(varchar(2), SUM(DATEDIFF(mi, c.FromTime, c.ToTime)) % 60) as 'Total Time Spent',
            CONVERT(varchar(4), AVG(DATEDIFF(mi, c.FromTime, c.ToTime)) / 60) + ':' +
            CASE WHEN AVG(DATEDIFF(mi, c.FromTime, c.ToTime)) % 60 < 10 THEN '0' ELSE '' END +
            CONVERT(varchar(2), AVG(DATEDIFF(mi, c.FromTime, c.ToTime)) % 60) as 'Average Call Time'
FROM        Calls c
JOIN        Users u ON u.UserID = c.TakenBy
WHERE       c.FromTime BETWEEN @FromDate AND @ToDate
GROUP BY    u.UserID, u.FirstName, u.LastName
ORDER BY    u.FirstName + ' ' + u.LastName

前面的 SQL 查询返回正确的“调用次数”，但无论调用次数如何，“总时间”和“平均时间”始终相同(这显然是错误的)。

我已经阅读并尝试在 SUM 中使用 CASE WHEN __ Then value ELSE 0 来实现，但它仍然返回不正确的值。

我可以让这个查询返回正确结果的唯一方法是，如果我完全去除所有其他信息，例如

SELECT SUM(DATEDIFF(mi, FromTime, ToTime)) FROM Calls WHERE c.FromTime BETWEEN...

我怎样才能仍然使用我的 JOIN 和 GROUP BY 并获得聚合函数来给我想要的结果？

感谢您的任何帮助!

Answer 1

使用子查询可能会更好，例如在 SELECT 部分，添加类​​似 (SELECT SUM(...) FROM ... WHERE ...) AS total_sum 的内容.