Average difference between two dates, grouped by a third field?
You don't specify the granularity you want for the diff. This does it in days:
select username, avg(end_date - start_date) as avg_days
from mytable
group by username
If you want the difference in seconds, use datediff()
:
select username, avg(datediff(ss, start_date, end_date)) as avg_seconds
...
datediff can measure the diff in any time unit up to years by varying the first parameter, which can be ss, mi, hh, dd, wk, mm or yy.
SELECT [username], AVG(TIMESTAMPDIFF(HOUR, start_date, end_date))
FROM [table]
GROUP BY [username]