Audio and Video File Extensions

The below SQL script counts the number of instances of file extensions of audio or video assets.

MS SQL Server

SELECT
    extension,
    COUNT(*) AS count
FROM (
    SELECT
        SUBSTRING(
            name,
            LEN(name) - CHARINDEX('.', REVERSE(name)) + 2,
            LEN(name)
        ) AS extension
    FROM asset WITH (NOLOCK)
    WHERE name LIKE '%.%' and type IN ('Audio', 'Video')
) AS derived_table
GROUP BY extension
ORDER BY count DESC;

PostgreSQL

SELECT
    extension,
    COUNT(*) AS count
FROM (
    SELECT
        SUBSTRING(
            name,
            LENGTH(name) - POSITION('.' IN REVERSE(name)) + 2,
            LENGTH(name)
        ) AS extension
    FROM asset
    WHERE name LIKE '%.%' AND type IN ('Audio', 'Video')
) AS derived_table
GROUP BY extension
ORDER BY count DESC;