Determining the top-20 in a given year from a last.fm scrobbles table

Question

Good afternoon!

I created a table in a Mariadb test base (XAMPP) to store all my scrobbles (times I listened to some music) from the site Last.FM. I used that tool to generate the CSV and imported the data to the table. The CREATE code (generated by heidisql) is as follows:

CREATE TABLE `scrobbles` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `artist` VARCHAR(128) NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
    `album` VARCHAR(128) NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
    `title` VARCHAR(128) NULL DEFAULT '' COLLATE 'utf8mb4_general_ci',
    `datahora` DATETIME NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

I would like to know now how to list the 20 most listened songs (columns title and artist duplicate) a given year.

Answer 1

Adapting of the reply of my reply in the world OS:

If you want for a single year, you can aggregate, classify and limit:

select artist, album, title, count(*) cnt
from scrobbles
where datahora >= '2019-01-01' and datahora < '2020-01-01'
group by artist, album, title
order by count(*) desc limit 100

I added the album field to prevent homonymous tracks from different albums from being grouped together.