I have a table with different IDs and for each ID there are two different times (Time1 and Time2). I need to check if Time1 is bigger than Time2 then I need to subtract X= Time1 -Time2. If Time2 is bigger I need to subtract Y=Time2 - Time1.
This is my result of the query:
ID Time1 Time2 Result
1 2018-02-12 08:00:00 2018-02-12 09:28:00 -00:32
2 2018-02-12 07:00:00 2018-02-12 08:04:00 -00:56
3 2018-02-12 06:00:00 2018-02-12 08:10:00 00:10
4 2018-02-12 06:00:00 2018-02-12 08:34:00 00:34
If Time1 is bigger I add a '-' in the output to differentiate. What I want now is to calculate the average of the result times but separately for the positive ones and negatives one. What I want is something like this:
(00:32 + 00:56) /2
and
(00:10 + 00:34) /2
Time1 and Time2 are stored as Datetimes but I convert them to strings because I just want to see the time difference I don't need dates. Is there a way to calculate the average?
use avg() to find the average. To find the average of negatives -
SELECT AVG(Result) FROM table_name WHERE Result like '-%';