0

I'm having problems with time average calculations. I have a table with a lot of records, the table structure is user id, begin and end time of work (if you want, you can download the table script here) and I need to get the effective working time average of each user, so I go like this for the average:

select iduser, sec_to_time(avg(timestampdiff(second, begin, end))) 
from test 
group by iduser;

So far so good, it returns:

USER1 00:25:55.9327
USER2 05:47:44.8713
USER3 03:13:43.4724

However, the problem is when I try to calculate the average of all users (no grouping):

select sec_to_time(avg(timestampdiff(second,begin, end))),avg(timestampdiff(second,begin, end))
from test;

it returns '03:26:18.9014', but according to my calculations, it should return 03:09:07.333. I checked with constant values and excel test

select sec_to_time((time_to_sec("00:25:55.9327")+time_to_sec("05:47:44.8713")+time_to_sec("3:13:43.4724"))/3);

So far I don't understand why it's returning '03:26:18.9014', it's innacurate according to the other calculations. Do anybody have an idea why is this happening?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Andino
  • 3
  • 2
  • Please provide a minimal reproducible example of the problem, as tabular text in the question, so one can investigate this. – GMB Feb 18 '20 at 15:28

2 Answers2

0

If you run this query

select  count(iduser) as cnt,
    sum(timestampdiff(SECOND, `begin`, `end`)) as tot,
    avg(timestampdiff(SECOND, `begin`, `end`)) as aver,
    sec_to_time(avg(timestampdiff(SECOND, `begin`, `end`))) as tim
from test ;

RESULT

cnt     tot         aver        tim
2903    35935951    12378.9015  03:26:18.9014

You get all the parts so that you can do your own calculations and it appears correct

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
0

I figured it out, it's statistic math basics, I can't get the same result if there are different number of records in the groups, each user would have to have the same number of records

Andino
  • 3
  • 2