[SQL] MySQL: Get Previous Years View Count

I have SQL that gets the past 60 days and the sum of view for each of these days and all works fine. I'm trying to take these past 60 days and get the same days view counts from the previous year.

So say for example today's record is (views, date):

234, 2024-10-07


I want to get this days record from last year, like:

75, 2023-10-07


Current SQL for the past 60 days view counts:

SELECT COALESCE(SUM(`adv_views`), 0) AS `total_views`
, STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC
LIMIT 0, 60;


I've modified the SQL to try and move it back a year based on the current 60 days, but it returns nothing:

SELECT COALESCE(SUM(`adv_views`), 0) AS `total_views`
, STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
WHERE STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') <= STR_TO_DATE(DATE_SUB(NOW(), INTERVAL 12 MONTH), '%Y-%m-%d')
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC
LIMIT 0, 60;


The table structure is:

adv_day, adv_month, adv_year, adv_views
'07', '10', '2024', '1'
'07', '10', '2024', '3'
'07', '10', '2024', '2'
'06', '10', '2024', '1'
'06', '10', '2024', '1'

Continue reading...