计算总加班MySQL

Question

我正在创建一个时钟系统和管理网站。我正在尝试使用MySQL查询拉取特定部门的加班金额。

时钟更新名为events的MySQL表，该表存储employee_id、它们正在处理的job、time_in和time_out。我可以使用以下命令生成每个employee_id的总工作时数表：

SELECT employee_id, 
       SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))) AS hours 
  FROM events 
 GROUP BY employee_id;

这将返回一个表，其中包含employee_id及其工作的总小时数。要创建employee_id加班时间的表，我使用：

SELECT employee_id,
       IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
          HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
          0) AS overtime 
  FROM events 
  GROUP BY employee_id;

这将返回一个表，其中包含employee_id及其加班时间。

当我想要计算所有员工的加班总数时，我遇到的麻烦就开始了。但是，当我运行以下语句时，我假设我可以将IF函数放在SUM()中来查找超时时间：

SELECT SUM(IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0)) AS overtime 
 FROM events;

我得到了这个错误：

ERROR 1111 (HY000): Invalid use of group function

我已经找遍了所有的地方，但是在SUM(IF(SUM()))函数方面几乎没有发现。

注意:加班必须单独计算，我不能只计算total hours worked / number of employees - 40。如果一个工人一周工作30个小时，另一个工人工作50个小时，那么就有10个小时的加班，但平均来说没有加班。

Answer 1

这个怎么样？

SELECT SUM(overtime)
  FROM
  (
    SELECT employee_id,
           IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0) AS overtime 
      FROM events 
     GROUP BY employee_id
  )TOTAL

当您使用SQL实现复杂的业务逻辑时，嵌套查询有一个关键的优势:它们允许您测试每个级别的逻辑并以可靠的方式构建它。

Answer 2

只需将这一点添加到奥利的上述答案中：

SELECT SUM(overtime)
  FROM
  (
    SELECT employee_id,
           IF(HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))>40,
              HOUR(SEC_TO_TIME(SUM(TIMEDIFF(time_out,time_in))))-40,
              0) AS overtime 
      FROM events
     GROUP BY employee_id
  ) TOTAL_OVERTIME

您需要命名派生表才能使代码正常工作。