Sort albums by total file size?

[witchseason]

Hi
Is there any way to sort my music collection according to the *total* file size of each album? I'd like to trim down the size of my collection on my HDD, and it occurred to me that I could delete some entire albums that I never listen to and that are taking up a lot of disk space.
I know I could select all the tracks on an album and see the total file size in the status bar, but it would be much easier if I didn't have to do that for each album in turn!
Thanks

[Lowlander]

No, you can only use Filesize, there is no Albumsize method.

[fetzenschaedl]

Hello,

you could use https://www.mediamonkey.com/addons/brow ... ql-editor/ and enter this SQL query

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
GROUP BY Albums.ID, Albums.Album
ORDER BY SUM(Songs.FileLength) DESC;

or if you only want Music Type

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
WHERE Songs.TrackType = 0
GROUP BY Albums.ID, Albums.Album
ORDER BY SUM(Songs.FileLength) DESC;

This should get you Artist, Album and Totalsize of the Album

[fetzenschaedl]

Hello,

you could use https://www.mediamonkey.com/addons/brow ... ql-editor/ and enter this SQL query

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
GROUP BY Albums.ID, Albums.Album
ORDER BY SUM(Songs.FileLength) DESC;

or if you only want Music Type

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
WHERE Songs.TrackType = 0
GROUP BY Albums.ID, Albums.Album
ORDER BY SUM(Songs.FileLength) DESC;

This should get you Artist, Album and Totalsize of the Album

You could also use these two SQL Queries, one for Albums with a TotalPlaycount under 10 and Albums you have not played in over a year, the results should be sorted by Totalsize of the Albums

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    COALESCE(SUM(Songs.PlayCounter), 0) AS TotalPlays,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
WHERE Songs.TrackType = 0
GROUP BY Albums.ID, Albums.Album
HAVING COALESCE(SUM(Songs.PlayCounter), 0) < 10
ORDER BY SUM(Songs.FileLength) DESC;

Code: Select all

SELECT
    MAX(Songs.AlbumArtist) AS AlbumArtist,
    Albums.Album,
    COALESCE(date(MAX(CASE WHEN Songs.LastTimePlayed > 1
                           THEN Songs.LastTimePlayed END) + 2415018.5),
             'never') AS LastPlayed,
    CASE
        WHEN SUM(Songs.FileLength) >= 1073741824
            THEN ROUND(SUM(Songs.FileLength) / 1073741824.0, 2) || ' GB'
        ELSE CAST(ROUND(SUM(Songs.FileLength) / 1048576.0) AS INTEGER) || ' MB'
    END AS TotalSize
FROM Albums
JOIN Songs ON Songs.IDAlbum = Albums.ID
WHERE Songs.TrackType = 0
GROUP BY Albums.ID, Albums.Album
HAVING
    MAX(CASE WHEN Songs.LastTimePlayed > 1 THEN Songs.LastTimePlayed END) IS NULL
    OR MAX(CASE WHEN Songs.LastTimePlayed > 1 THEN Songs.LastTimePlayed END)
       < (julianday('now') - 2415018.5 - 365)
ORDER BY SUM(Songs.FileLength) DESC;

[witchseason]

These are all great - thanks!

[IanRTaylorUK]

Similar to fetzenschaedl:

Code: Select all

SELECT 
    COALESCE(Album, 'Unknown Album') AS Album,
    COALESCE(AlbumArtist, 'Unknown Artist') AS AlbumArtist,
    ROUND(SUM(FileLength) / 1024.0 / 1024.0, 2) AS TotalSizeMB
FROM Songs
WHERE TrackType = 0
GROUP BY Album, AlbumArtist
ORDER BY TotalSizeMB DESC;

Is a slightly shorter SQL for SQL Browser.

• TrackType = 0: Ensures that the query strictly targets Music tracks (filtering out audiobooks, podcasts, videos, etc. based on MediaMonkey's standard type mapping).

• SUM(FileLength): Adds together the byte size (FileLength) of all tracks belonging to each album.

• Conversion & Rounding: Divides the total bytes by 1024.0 twice to convert them into Megabytes (MB) and uses ROUND(..., 2) to keep the numbers clean and readable.

• COALESCE: Protects against blank or NULL album and artist names by assigning standard fallback labels.

I wonder if we can get this to work in the Custom Nodes add-on?

[fetzenschaedl]

This should work in Custom Nodes, Top 50 Albums by Totalfilesize. Keep in mind that the albums are not sorted by Totalfilesize in the Custom Nodes Album View.

Code: Select all

SELECT * FROM Albums
WHERE ID IN (
    SELECT IDAlbum FROM Songs
    WHERE TrackType = 0 AND IDAlbum > 0
    GROUP BY IDAlbum
    ORDER BY SUM(FileLength) DESC
    LIMIT 50
)
ORDER BY (SELECT SUM(FileLength) FROM Songs
          WHERE Songs.IDAlbum = Albums.ID AND Songs.TrackType = 0) DESC