I have a table that keeps track of runs. It has the following columns:

I want to calculate the average pace per mile (in the format mm:ss) inside a certain date range. For instance, with a date range from 12/04-12/05, the pace per mile would be 09:48. The first step would be to figure out the total number of miles and minutes. Then divide the number of minutes by the total number of miles. The last step would be to convert the number of minutes to the mm:ss format.

Here is what I was able to come up with so far:

with average as (
select
    runTime.totalTime / distance.totalDistance as averagePace
from
    ((
    select
        sum(distance) as totalDistance
    from
        runs
    where
        run_date >= '2023-12-04'
        and run_date <= '2023-12-05') ) as distance,
    (
    select
        extract / 60 as totalTime
    from
        (
        select
            extract(EPOCH
        from
            sum )
        from
            (
            select
                sum(run_time)
            from
                runs
            where
                run_date >= '2023-12-04'
                and run_date <= '2023-12-05'))) as runTime) 
select
    TO_CHAR((averagePace || ' minute')::interval,
    'MI:SS')
from
    average

The query calculates the correct pace, but I feel like it doesn't need to be this long. Is there a cleaner, more-efficient way of calculating the average pace per mile?

CodePudding user response：

If your run_time is stored as the interval datatype, then this will work:

select to_char(sum(run_time) / sum(distance), 'mi:ss')
  from runs
 where run_date between '2023-12-04' and '2023-12-05';

Please see this fiddle.