SELECT
pageName,
publishTime,
title,
link,
videoLength,
views,
minPerView,
totalWatchTime,
avgTimeWatched,
likeCount,
dislikeCount,
commentCount

FROM (
SELECT
name AS pageName,
youtubeVideos.publishTime,
youtubeVideos.title,
youtubeVideos.link,
SUBSTR(duration*1.0/60, 1, INSTR(duration*1.0/60, \".\")-1) || \":\" || CASE WHEN LENGTH(CAST((60*CAST(\"0\" || SUBSTR(duration*1.0/60, INSTR(duration*1.0/60, \".\"), LENGTH(duration*1.0/60)) AS decimal)) AS integer)) = 1 THEN \"0\" || CAST((60*CAST(\"0\" || SUBSTR(duration*1.0/60, INSTR(duration*1.0/60, \".\"), LENGTH(duration*1.0/60)) AS decimal)) AS integer) ELSE CAST((60*CAST(\"0\" || SUBSTR(duration*1.0/60, INSTR(duration*1.0/60, \".\"), LENGTH(duration*1.0/60)) AS decimal)) AS integer) END AS videoLength,
youtubeAnalyticsVideos.views, ROUND(((averageViewDuration* youtubeAnalyticsVideos.views)*1.0/60)/ youtubeAnalyticsVideos.views,2) AS minPerView,
ROUND(((averageViewDuration* youtubeAnalyticsVideos.views)*1.0/60),0) AS totalWatchTime,
PRINTF(\"%.2f\",ROUND(averageViewDuration*1.0/duration*100,2)) AS avgTimeWatched,
youtubeVideos.likes AS likeCount,
youtubeVideos.dislikes AS dislikeCount,
youtubeVideos.comments AS commentCount FROM youtubeVideos
LEFT JOIN youtubeAnalyticsVideos ON youtubeAnalyticsVideos.link=youtubeVideos.link INNER JOIN profiles ON youtubeVideos.profileId=profiles.id ORDER BY avgTimeWatched DESC) AS dataTable UNION SELECT
'Videos' AS pageName, 'Summe' AS publishTime, 'Summe der Werte über alle neuen Videos' AS title, NULL AS link, SUBSTR(AVG(duration)*1.0/60, 1, INSTR(AVG(duration)*1.0/60, \".\")-1) || \":\" || CASE WHEN LENGTH(CAST((60*CAST(\"0\" || SUBSTR(AVG(duration)*1.0/60, INSTR(AVG(duration)*1.0/60, \".\"), LENGTH(AVG(duration)*1.0/60)) AS decimal)) AS integer)) = 1 THEN \"0\" || CAST((60*CAST(\"0\" || SUBSTR(AVG(duration)*1.0/60, INSTR(AVG(duration)*1.0/60, \".\"), LENGTH(AVG(duration)*1.0/60)) AS decimal)) AS integer) ELSE CAST((60*CAST(\"0\" || SUBSTR(AVG(duration)*1.0/60, INSTR(AVG(duration)*1.0/60, \".\"), LENGTH(AVG(duration)*1.0/60)) AS decimal)) AS integer) END AS videoLength,\n\tSUM(youtubeAnalyticsVideos.
) AS views,\n\tNULL AS minPerView,\n\tSUM(ROUND(((averageViewDuration* youtubeAnalyticsVideos.views)*1.0/60),0)) AS totalWatchTime,\n\tNULL AS avgTimeWatched,\n\tSUM(youtubeVideos.likes) AS likeCount, \n\tSUM(youtubeVideos.dislikes) AS dislikeCount, \n\tSUM(youtubeVideos.comments) AS commentCount\nFROM youtubeVideos\nLEFT JOIN youtubeAnalyticsVideos ON youtubeAnalyticsVideos.link=youtubeVideos.link \nINNER JOIN profiles ON youtubeVideos.profileId=profiles.id