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?
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