How to sumarize correctly as "Sickness time" from other table - mysql

Question

In mysql database i've created "Sickness" table:

    +--------+---------+---------+-------------+---------+-------------------------------+
    | Id_SICK|ID_WORKER| FNAME   | LNAME   | BEGIN_DATE          | END_DATE              |
    +--------+---------+---------+---------+------------+--------------------+-----------+
    | 4      |   26    | ANDREW  | WORM    |2019-03-19 07:00:00  |2019-03-19 15:00:00    |  
    +--------+---------+---------+----------------------+--------------------+-----------+  
    | 5      |   25    | ADAM    | GAX     |2019-03-21 07:00:00  |2019-03-21 15:00:00    |  
    +--------+---------+---------+----------------------+--------------------------------+  

"Workers" table:

+--------+---------+---------+--
|ID_WORKER |  FNAME  | LNAME   |
+----------+---------+----------
| 25       |  ADAM   |  GAX    |
+----------+---------+----------
| 26       |  ANDREW |  WORM   |
+----------+---------+----------

"Orders" table:

+--------+---------+---------+------------+
|ID_ORDER  |  DESC_ORDER  | NUMBER_ORDER  |
+----------+---------+--------------------+
| 20       |  TEST        |  TEST         |
+----------+---------+--------------------+

And "Order_status" table:

+--------+---------+---------+---------+-------------+--------+----------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE          | END_DATE          | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
| 47       |   25    |    20   |2019-03-18 06:50:35  |2019-03-18 15:21:32|  NO        |
+----------+---------+---------+------------+---------+-------------------+-----------+ 
| 48       |   25    |    20   |2019-03-20 06:44:12  |2019-03-20 15:11:23|  NO        |
+----------+---------+---------+------------+---------+-------------------+-----------+ 
| 50       |   25    |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES        |
+----------+---------+---------+------------+---------+-------------------+-----------+ 
| 51       |   26    |    20   |2019-03-18 06:45:11  |2019-03-18 15:14:45|  NO        |
+----------+---------+---------+------------+---------+-------------------+-----------+ 
| 52       |   26    |    20   |2019-03-20 06:50:22  |2019-03-20 15:10:32|  NO       |
+----------+---------+---------+------------+---------+-------------------+-----------+ 
| 53       |   25    |    20   |2019-03-22 06:54:11  |2019-03-22 11:23:45|  YES       |
+----------+---------+---------+------------+---------+-------------------+-----------+ 

I would like to sumarize "total time" of each other workers (in order_status table) on the order including with sumarizing "sickness time" from Sickness table. I have selected workers (LNAME, FNAME) orders (DESC_ORDER and NUMBER_ORDER) and "TOTAL TIME" on order from each other workers correctly. But i'm not able to sumarize sickness time. I wrote the mysql command in below:

SELECT workers.FNAME, workers.LNAME, orders.NUMBER_ORDER, orders.DESC_ORDER, SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS 'TOTAL TIME', SEC_TO_TIME(SUM(TIME_TO_SEC(sickness.END_DATE) - TIME_TO_SEC(sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM order_status INNER JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER INNER JOIN sickness ON sickness.ID_WORKER = workers.ID_WORKER WHERE orders.NUMBER_ORDER LIKE 'TEST' GROUP BY workers.ID_WORKER

Then i've got that result:

+--------+---------+---------+-------+------------+------------+-------------+
|  FNAME  | LNAME   |  NUMBER_ORDER  | DESC_ORDER | TOTAL TIME | SICKNESS_TIME|
+----------+---------+---------------+------------+------------+-------------+
|  ADAM   |  GAX    | TEST           | TEST       | 22:25:38   |   24:00:00   |
+----------+---------+---------------+------------+------------+-------------+
|  ANDREW |  WORM   | TEST           | TEST       | 22:52:12   |   24:00:00   |
+----------+---------+---------------+------------+------------+-------------+

As for as "Sickness time is incorrect" because from the "Sickness" after grouping ID_SICK is

+--------+---------+-----+
| Id_SICK| SICKNESS TIME |
+--------+---------+-----+
| 4      |   08:00:00    |  
+--------+---------+-----+ 
| 5      |   08:00:00    |   
+--------+---------+-----+  

I have sumarize "TOTAL TIME + SICKNESS TIME" too for example

TOTAL TIME: 22:25:38 
SICKNESS TIME: 8:00:00

TOTAL + SICKNESS TIME : 22;25:38 + 8:00:00 = 30:25:38

Can someone please help me how to deal with? What kind of mysql query should i write? Any ideas? Thx for any help :)

Answer 1

This is actually expected. The above query is joining 4 tables:

• order_status
• sickness
• workers
• orders

"workers" table has a 1:N relation to "order_status".

Also "workers" has 1:N relation to sickness, actually it does not matter even if this is 1:1.

The query is creating a Cartesian product between the above tables and duplicate column values from each table can be in the result set of the query above.

Remove the group by and the sum to see the result set.

eg in your example for id_worker 25, you have 3 order_status rows joined with 1 sickness row (this row values will be duplicated 3 times), joined with 1 workers row (again they will duplicated 3 times), joined with 1 orders row (same).

So the aggregate function sum combines duplicate values.

This would only work if result set contained unique rows for all columns used for aggregate functions

To solve this, use sub-queries to aggregate the results:

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(sicknessAgg.stime)) AS 'SICKNESS TIME' 
FROM   workers 
INNER JOIN (
SELECT sickness.id_worker, SUM((Time_to_sec(sickness.end_date) - 
                       Time_to_sec(sickness.begin_date))) AS stime
FROM sickness
GROUP BY sickness.id_worker
) sicknessAgg
               ON sicknessAgg.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

Note that in this case, it is needed to start from the workers table, as you want to aggregate a different number of rows from order_status and sickness tables.

References:

How to join three tables to get Sum

MySQL JOIN with multiple tables and SUMS

How get the sum of two tables value using inner join