title: How to sumarize correctly as “Leave Time” + "Total Time" - mysql
body: <p>OK - i've changed that concept. In mysql database i've created "leave" table:</p>
  
  <pre><code>+--------+---------+---------+-------------+---------+-------------------------------+
  |ID_LEAVE|ID_WORKER| FNAME   | LNAME   | BEGIN_DATE          | END_DATE              | 
  +--------+---------+---------+---------+------------+--------------------+-----------
  | 1      |   1     | DAVID   | BUCS    |2019-03-19 07:00:00  |2019-03-19 15:00:00    | 
  +--------+---------+---------+----------------------+--------------------+----------- 
  | 2      |   2     | MARK    | GREEN   |2019-03-21 07:00:00  |2019-03-21 15:00:00    |
  +--------+---------+---------+----------------------+--------------------------------+
  </code></pre>
  
  <p>"Workers" table:</p>
  
  <pre><code>+----------+---------+---------+
  |ID_WORKER |  FNAME  | LNAME   |
  +----------+---------+----------
  | 1        |  DAVID  |  BUCS   |
  +----------+---------+----------
  | 2        |  MARK   |  GREEN  |
  +----------+---------+---------+
  </code></pre>
  
  <p>"Orders" table:</p>
  
  <pre><code>+----------+--------------+---------------+
  |ID_ORDER  |  DESC_ORDER  | NUMBER_ORDER  |
  +----------+--------------+---------------+
  | 20       |  TEST        |  TEST         |
  +----------+--------------+---------------+
  </code></pre>
  
  <p>"Order_status" table:</p>
  
  <pre><code>+----------+---------+---------+---------------------+-------------------+------------+
  | Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE          | END_DATE          | ORDER_DONE |
  +----------+---------+---------+----------+------------+---------+--------------------+
  | 30       |   1     |    20   |2019-03-18 06:50:35  |2019-03-18 15:21:32|  NO        |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  | 31       |   1     |    20   |2019-03-20 06:44:12  |2019-03-20 15:11:23|  NO        |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  | 32       |   1     |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES       |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  | 33       |   2     |    20   |2019-03-18 06:45:11  |2019-03-18 15:14:45|  NO        |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  | 34       |   2     |    20   |2019-03-20 06:50:22  |2019-03-20 15:10:32|  NO        |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  | 35       |   2     |    20   |2019-03-22 06:54:11  |2019-03-22 11:23:45|  YES       |
  +----------+---------+---------+------------+---------+-------------------+-----------+ 
  </code></pre>
  
  <p>What i've done:</p>
  
  <p>I can to sumarize "total time" of each other workers (in order_status table) on the order including with sumarizing "leave time" from Leave table. I have selected workers (LNAME, FNAME) orders (DESC_ORDER and NUMBER_ORDER) and "TOTAL TIME" on order from each other workers correctly too. I wrote the mysql command in below:</p>
  
  <pre><code>SELECT workers.fname, 
         workers.lname, 
         order_statusAgg.number_order,
         workers.id_worker,
         order_statusAgg.desc_order, 
         SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'TOTAL TIME', 
         SEC_TO_TIME(SUM(leaveAgg.ltime)) AS 'LEAVE TIME' 
  FROM   workers 
  INNER JOIN (
  SELECT leave.id_worker, SUM((Time_to_sec(leave.end_date) - 
                         Time_to_sec(leave.begin_date))) AS ltime
  FROM leave
  GROUP BY leave.id_worker
  ) leaveAgg
                 ON leaveAgg.id_worker = workers.id_worker
         INNER JOIN (
  SELECT order_status.id_worker, orders.number_order, orders.desc_order, SUM((Time_to_sec(order_status.end_date) - 
                         Time_to_sec(order_status.begin_date))) AS stime
  FROM order_status
             INNER JOIN orders 
                 ON orders.id_order = order_status.id_order
  GROUP BY order_status.id_worker
  ) order_statusAgg
                 ON workers.id_worker = order_statusAgg.id_worker 
  
  WHERE  order_statusAgg.number_order LIKE 'TEST'
  GROUP BY workers.id_worker
  </code></pre>
  
  <p>Then after that command i get:</p>
  
  <pre><code>    +---------+---------+---------------+------------+------------+--------------+
      |  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME   | 
      +---------+---------+---------------+------------+------------+--------------+
      |  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |   8:00:00    |   
      +---------+---------+---------------+------------+------------+--------------+
      |  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   8:00:00    |   
      +---------+---------+---------------+------------+------------+--------------+
  </code></pre>
  
  <p>what i'd like to do next:</p>
  
  <p>What i'd like to do next: I'm trying sumarize all of them, i mean: TOTAL TIME + LEAVE_TIME for each Worker. For example:</p>
  
  <pre><code> +---------+---------+---------------+------------+------------+-------------+----------+
  |  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME   | SUM_TIME |
  +---------+---------+---------------+------------+------------+--------------+----------+
  |  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |   8:00:00    | 30:30:21 |
  +---------+---------+---------------+------------+------------+--------------+----------+
  |  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   8:00:00    | 29:19:18 | 
  +---------+---------+---------------+------------+------------+--------------+----------+
  </code></pre>
  
  <p>i've tried add to at the top query:</p>
  
  <pre><code>SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
  </code></pre>
  
  <p>But i know that's a bad idea. So i have no clue how to solve it. Has someone idea how to solve it? Any ideas. Thx very much for any help!</p>