Mysql Question posted in
The official MySQL documentation can be found here.

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'
Tags:

2

Answers


  1. 0

    Your WHERE clause seems to be the one causing the issue. You are trying to compare the dates using DATE_SUB(NOW(), INTERVAL 12 MONTH) but filtering the days, months and years separately, which don’t go aligned.

    WHERE STR_TO_DATE(CONCAT(adv_year, ‘-‘, adv_month, ‘-‘, adv_day), ‘%Y-%m-%d’) BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND DATE_SUB(CURDATE(), INTERVAL 12 MONTH – INTERVAL 60 DAY)

    Change your WHERE and BETWEEN clause with this one and I’m hopeful that you’ll get your results.

    Login or Signup to reply.
  2. 0

    Try this,

    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') BETWEEN DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND CURDATE()
  OR STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR + 60 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY
  `adv_year`, `adv_month`, `adv_day`
ORDER BY
  `date` DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search