How to sumarize correctly as “Leave Time” + “Total Time” - mysqlCalculate relative time in C#How can I prevent SQL injection in PHP?How do you set a default value for a MySQL Datetime column?How do you get a timestamp in JavaScript?Should I use the datetime or timestamp data type in MySQL?How to get the current time in PythonDaylight saving time and time zone best practicesHow to import an SQL file using the command line in MySQL?How to sumarize correctly as “Sickness time” from other table - mysqlHow to sumarize correctly: “Total Time” + “Sickness Time” + “Vacation Time” - mysql
Inappropriate reference requests from Journal reviewers
What is the intuitive meaning of having a linear relationship between the logs of two variables?
Go Pregnant or Go Home
Would a high gravity rocky planet be guaranteed to have an atmosphere?
Is it appropriate to ask a job candidate if we can record their interview?
Why are there no referendums in the US?
Different result between scanning in Epson's "color negative film" mode and scanning in positive -> invert curve in post?
Pole-zeros of a real-valued causal FIR system
Is there a korbon needed for conversion?
Arithmetic mean geometric mean inequality unclear
How to run a prison with the smallest amount of guards?
How to be diplomatic in refusing to write code that breaches the privacy of our users
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
Lay out the Carpet
Crossing the line between justified force and brutality
How do I rename a Linux host without needing to reboot for the rename to take effect?
Avoiding estate tax by giving multiple gifts
Where does the Z80 processor start executing from?
Anatomically Correct Strange Women In Ponds Distributing Swords
Flow chart document symbol
System.debug(JSON.Serialize(o)) Not longer shows full string
What is paid subscription needed for in Mortal Kombat 11?
I'm in charge of equipment buying but no one's ever happy with what I choose. How to fix this?
What is the best translation for "slot" in the context of multiplayer video games?
How to sumarize correctly as “Leave Time” + “Total Time” - mysql
Calculate relative time in C#How can I prevent SQL injection in PHP?How do you set a default value for a MySQL Datetime column?How do you get a timestamp in JavaScript?Should I use the datetime or timestamp data type in MySQL?How to get the current time in PythonDaylight saving time and time zone best practicesHow to import an SQL file using the command line in MySQL?How to sumarize correctly as “Sickness time” from other table - mysqlHow to sumarize correctly: “Total Time” + “Sickness Time” + “Vacation Time” - mysql
0
OK - i've changed that concept. In mysql database i've created "leave" table:
+--------+---------+---------+-------------+---------+-------------------------------+
|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 |
+--------+---------+---------+----------------------+--------------------------------+
"Workers" table:
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 1 | DAVID | BUCS |
+----------+---------+----------
| 2 | MARK | GREEN |
+----------+---------+---------+
"Orders" table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
"Order_status" table:
+----------+---------+---------+---------------------+-------------------+------------+
| 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 |
+----------+---------+---------+------------+---------+-------------------+-----------+
What i've done:
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:
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
Then after that command i get:
+---------+---------+---------------+------------+------------+--------------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+
what i'd like to do next:
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:
+---------+---------+---------------+------------+------------+-------------+----------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+----------+
i've tried add to at the top query:
SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
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!
mysql datetime sum
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
add a comment |
0
OK - i've changed that concept. In mysql database i've created "leave" table:
+--------+---------+---------+-------------+---------+-------------------------------+
|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 |
+--------+---------+---------+----------------------+--------------------------------+
"Workers" table:
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 1 | DAVID | BUCS |
+----------+---------+----------
| 2 | MARK | GREEN |
+----------+---------+---------+
"Orders" table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
"Order_status" table:
+----------+---------+---------+---------------------+-------------------+------------+
| 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 |
+----------+---------+---------+------------+---------+-------------------+-----------+
What i've done:
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:
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
Then after that command i get:
+---------+---------+---------------+------------+------------+--------------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+
what i'd like to do next:
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:
+---------+---------+---------------+------------+------------+-------------+----------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+----------+
i've tried add to at the top query:
SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
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!
mysql datetime sum
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
Use properGROUP BY. That code will not run in any dbms system, except maybe on old MySQL.
– Eric
Mar 21 at 16:15
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 Let's take your inner most querySELECT 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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.
– Eric
Mar 21 at 19:20
add a comment |
0
0
0
OK - i've changed that concept. In mysql database i've created "leave" table:
+--------+---------+---------+-------------+---------+-------------------------------+
|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 |
+--------+---------+---------+----------------------+--------------------------------+
"Workers" table:
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 1 | DAVID | BUCS |
+----------+---------+----------
| 2 | MARK | GREEN |
+----------+---------+---------+
"Orders" table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
"Order_status" table:
+----------+---------+---------+---------------------+-------------------+------------+
| 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 |
+----------+---------+---------+------------+---------+-------------------+-----------+
What i've done:
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:
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
Then after that command i get:
+---------+---------+---------------+------------+------------+--------------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+
what i'd like to do next:
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:
+---------+---------+---------------+------------+------------+-------------+----------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+----------+
i've tried add to at the top query:
SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
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!
mysql datetime sum
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
OK - i've changed that concept. In mysql database i've created "leave" table:
+--------+---------+---------+-------------+---------+-------------------------------+
|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 |
+--------+---------+---------+----------------------+--------------------------------+
"Workers" table:
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 1 | DAVID | BUCS |
+----------+---------+----------
| 2 | MARK | GREEN |
+----------+---------+---------+
"Orders" table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
"Order_status" table:
+----------+---------+---------+---------------------+-------------------+------------+
| 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 |
+----------+---------+---------+------------+---------+-------------------+-----------+
What i've done:
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:
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
Then after that command i get:
+---------+---------+---------------+------------+------------+--------------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+
what i'd like to do next:
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:
+---------+---------+---------------+------------+------------+-------------+----------+
| 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 |
+---------+---------+---------------+------------+------------+--------------+----------+
i've tried add to at the top query:
SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
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!
mysql datetime sum
mysql datetime sum
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
asked Mar 21 at 15:55
Prochu1991Prochu1991
317
317
Use properGROUP BY. That code will not run in any dbms system, except maybe on old MySQL.
– Eric
Mar 21 at 16:15
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 Let's take your inner most querySELECT 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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.
– Eric
Mar 21 at 19:20
add a comment |
Use properGROUP BY. That code will not run in any dbms system, except maybe on old MySQL.
– Eric
Mar 21 at 16:15
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 Let's take your inner most querySELECT 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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.
– Eric
Mar 21 at 19:20
Use proper
GROUP BY. That code will not run in any dbms system, except maybe on old MySQL.– Eric
Mar 21 at 16:15
Use proper
GROUP BY. That code will not run in any dbms system, except maybe on old MySQL.– Eric
Mar 21 at 16:15
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 Let's take your inner most query
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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.– Eric
Mar 21 at 19:20
@Prochu1991 Let's take your inner most query
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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.– Eric
Mar 21 at 19:20
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55284460%2fhow-to-sumarize-correctly-as-leave-time-total-time-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55284460%2fhow-to-sumarize-correctly-as-leave-time-total-time-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Use proper
GROUP BY. That code will not run in any dbms system, except maybe on old MySQL.– Eric
Mar 21 at 16:15
Looks like a step in the right direction to me. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Mar 21 at 17:00
@Eric But i wrote today that code without my trying new code and that worked
– Prochu1991
Mar 21 at 19:10
@Prochu1991 It may work. It doesn't mean you should do it. There's a reason why it's not allowed by other dbms, including newer version of MySQL.
– Eric
Mar 21 at 19:14
@Prochu1991 Let's take your inner most query
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. If this worker has multiple orders, which orders will be included when you are grouping by worker? MySQL would just randomly pick an order.– Eric
Mar 21 at 19:20