audio-clips.sql

audio-clips.sql

SELECT
    YEAR(cl.created),
    CASE
        WHEN MONTH(v.created) = '1' THEN 'Jan'
        WHEN MONTH(v.created) = '2' THEN 'Feb'
        WHEN MONTH(v.created) = '3' THEN 'Mar'
        WHEN MONTH(v.created) = '4' THEN 'Apr'
        WHEN MONTH(v.created) = '5' THEN 'May'
        WHEN MONTH(v.created) = '6' THEN 'Jun'
        WHEN MONTH(v.created) = '7' THEN 'Jul'
        WHEN MONTH(v.created) = '8' THEN 'Aug'
        WHEN MONTH(v.created) = '9' THEN 'Sep'
        WHEN MONTH(v.created) = '10' THEN 'Oct'
        WHEN MONTH(v.created) = '11' THEN 'Nov'
        WHEN MONTH(v.created) = '12' THEN 'Dec'
    END AS 'month',
    FLOOR((SUM(v.duration) / 60) / 60) AS 'aggregate_version_durations'
FROM
    clip cl
        JOIN
    pip_pip pp ON pp.parent_pid = cl.pid
        JOIN
    version v ON v.pid = pp.child_pid
        JOIN
    version_version_type vvt ON vvt.pid = v.pid
        AND vvt.version_type_id = 'Original'
WHERE
    cl.media_type = 'audio'
        AND YEAR(cl.created) = '2017'
GROUP BY MONTH(cl.created)