MySQL 查询。带条件提取 Sum(total) 和 Sum(total)

Question

关于我的问题 SQL 是，

我有一个表，用户在其中输入时间报告数据，称为 time_report。每个 time_report 都输入一个服务代码，每个服务代码都有不同的含义，包括开始、停止、时间和总计。

以下是在 time_report 表中输入的数据示例。

t_id Date User Customer Service Closing System  Part    Start   Stop    Total

53 2011-05-02 13 0   48 4   0   0   09:00   17.15   8.15

54 2011-05-03 13 0   49 4   0   0   09:00   17:00    8

55 2011-05-04 13 0   48 4   0   0   09:00   17.15   8:15

61 2011-05-04 1  0  52  4   0   0   09:00   17.15   8:15

62 2011-05-05 1  0  48  4   0   0   09:00   17.15   8:15

我正在尝试运行查询来提取 3 条信息。

user ( the user is the foreign key to users.user_id)
sum(total) per user.
sun(total) per user where service < 49 
Utilization Percentage: (This will be calculated by dividing the 2 totals )

服务是 servicecodes.s_id 的外键

当我尝试运行查询来获取总和时（总计，它工作正常，但我无法将信息分组在一起

mysql> SELECT  Users.full_name,sum(total)

    -> FROM time_report, users
    -> WHERE time_report.User = users.user_id
    -> AND date
    -> BETWEEN '2011-0502'
    -> AND '2011-05-11'
    -> GROUP BY User;

+-----------------+------------+
| full_name       | sum(total) |
+-----------------+------------+
| Cian Higgins    |         26 |
| Wallace Ward    |         23 |
| jason ward      |         42 |
| Thomas Woods    |         72 |
| Peter Jones     |         49 |
| fintan corrigan |         40 |
| David Jones     |         35 |
| January Jones   |         23 |
| Joe Johnson     |         24 |
+-----------------+------------+
9 rows in set, 1 warning (0.09)

当我使用服务 <49 运行查询时

SELECT  Users.full_name,sum(total) AS Productive

FROM time_report, users
WHERE time_report.User = users.user_id
AND date
BETWEEN '2011-0502'
AND '2011-05-11'
AND Service < 49
GROUP BY User;
+-----------------+------------+
| full_name       | Productive |
+-----------------+------------+
| Cian Higgins    |         14 |
| Wallace Ward    |         23 |
| jason ward      |         33 |
| Thomas Woods    |         53 |
| Peter Jones     |         41 |
| fintan corrigan |         32 |
| David Jones     |         27 |
| January Jones   |         23 |
| Joe Johnson     |         24 |
+-----------------+------------+
9 rows in set, 1 warning (0.03 sec)

但是，如果我尝试加入报告，我会收到错误......此外，如果我尝试将其作为子查询运行，我也会收到错误

示例：

SELECT Users.full_name, sum( total ) 
FROM time_report, users
WHERE time_report.User = users.user_id
AND date
BETWEEN '2011-0502'
AND '2011-05-11'
GROUP BY User
UNION 
SELECT Users.full_name, sum( total ) AS Productive
FROM time_report, users
WHERE time_report.User = users.user_id
AND date
BETWEEN '2011-0502'
AND '2011-05-11'
AND Service <49
GROUP BY User 

这给出了一行中的所有结果

full_name sum(total) 
Cian Higgins 26 
Wallace Ward 23 
jason ward 42 
Thomas Woods 72
 Peter Jones 49
 fintan corrigan 40 
David Jones 35 
January Jones 23 
Joe Johnson 24 
Cian Higgins 14 
jason ward 33 
Thomas Woods 53
 Peter Jones 41 
fintan corrigan 32 
David Jones 27 

Answer 1

您可以使用 CASE 语句来细化总和并通过一份报表返回两笔金额。

SELECT  Users.full_name
        ,sum(total)
        ,sum(case when service < 49 then total else 0 end) AS Productive
FROM    time_report
        , users
WHERE   time_report.User = users.user_id
        AND date BETWEEN '2011-0502' AND '2011-05-11'
GROUP BY 
        User