 | 1 | initial version |
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)
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.