Ask Your Question

Revision history [back]

I thought of this solution into 3 phases, 3 queries:

Query 1: turns everything in seconds

SELECT HOUR( "Length" ) * 3600 + MINUTE( "Length" ) * 60 + SECOND( "Length" ) "Seconds" FROM "Table1"

Query 2: averages the values entered in seconds (the FLOOR function truncates the result to a smaller integer)

SELECT FLOOR( AVG( "Seconds" ) ) "Media" FROM "Query1"

Query3: calculates and convert the format result from seconds to HH:MM:SS (function CAST (...) AS INTEGER, formats the number to integer)

SELECT CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) || ':' || CAST( FLOOR( "Media" / 60 ) AS INTEGER ) - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 60 || ':' || CAST( ( "Media" - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 3600 - ( CAST( FLOOR( "Media" / 60 ) AS INTEGER ) - CAST( FLOOR( "Media" / 3600 ) AS INTEGER ) * 60 ) * 60 ) AS INTEGER ) "Media Time" FROM "Query2"

A little complicated, but it works !

If my answer helped you, vote it with ✔ and with ∧ (top on the left)