SQL: finding the average number of days between runs for each runner

Question

So, if we have the following table:

runner ran Carol 2011-02-01 Alice 2011-02-01 Bob 2011-02-01 Carol 2011-02-02 Bob 2011-02-02 Bob 2011-02-03 Bob 2011-02-04 Carol 2011-02-07 Alice 2011-02-08 

How can I write a query (without any subquery) to find the average number of days that each runner should wait between runs (i.e. Carol waited 1 day and then 5, so the average is 3, Bob works every day, Alice waited 7 days)?

I thought about joining on the table itself, then I find max and min for each runner, subtracting them and dividing by the number of runs - 1. But how can I combine all this without any subquery?

Answer 1

Sorin, to be fair, you already have the answer - (max-min)/(count-1) really correct, without going into details of how far the runs are from each other.

 select runner, datediff(max(ran),min(ran)) / (count(ran)-1) from running group by runner; 

Note. MySQL will turn X / 0 (for which there is only one entry for the runner) to NULL, because it is indivisible by 0.