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

Mysql – Counting number of rows between 2 dates and 1 where clause

I’ve a mysql table with me

enter image description here

Now we want to do some calculations like this

  • count date wise for all courses enrolled
  • count where course id = 2 for date > start_date AND date < end_date

Expected output where we calculate all courses enrolled

Expected output where we calculate all courses enrolled

Expected output where we calculate all courses enrolled where course id = 2

*enter image description here

expected output where course_id = 2 AND date range is between 2022-11-15 to 2022-11-13

enter image description here

The query which I’ve right now

SELECT COUNT(*), DATE(registered_on) 
FROM courses_enrolled 
WHERE course_id = 1 
GROUP BY DATE(registered_on), course_id 
ORDER BY registered_on desc;
Tags:

2

Answers


  1. 0

    You need to use some kind of calendar table approach here:

    SELECT d.dt AS date, COUNT(ce.id) AS cnt
FROM (
    SELECT '2022-11-12' AS dt UNION ALL
    SELECT '2022-11-13' UNION ALL
    SELECT '2022-11-14' UNION ALL
    SELECT '2022-11-15'
) d
LEFT JOIN courses_enrolled ce
   ON DATE(ce.registered_on) = d.dt AND
      ce.course_id = 2
GROUP BY d.dt
ORDER BY d.dt;

    The calendar table ensures that all dates you want in the output appear. In practice, you may replace the subquery in d with a bona-fide table containing all dates of interest. The left join ensures that no dates are dropped which have no matching courses on that day.

    Login or Signup to reply.
  2. 0

    If you are using MySQL 8 you can use a recursive CTE to create your date range.

    For all enrolled courses for given date range –

    WITH RECURSIVE calendar (date) AS (
    SELECT '2022-11-13' # start date
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM calendar
    WHERE date + INTERVAL 1 DAY <= '2022-11-15' # end date
)
SELECT COUNT(ce.id) count_all, c.date
FROM calendar c
LEFT JOIN courses_enrolled ce
    ON ce.registered_on BETWEEN c.date AND (c.date + INTERVAL 1 DAY - INTERVAL 1 SECOND)
GROUP BY c.date
ORDER BY c.date DESC;

    Note the use of BETWEEN start AND end of day in the join criteria. For a small dataset this offers negligible benefit but on a large dataset it would allow for use of an index on registered_on, which could offer significantly improved performance.

    Or for just the selected course –

    WITH RECURSIVE calendar (date) AS (
    SELECT '2022-11-13' # start date
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM calendar
    WHERE date + INTERVAL 1 DAY <= '2022-11-15' # end date
)
SELECT COUNT(ce.id) count_selected_course, c.date
FROM calendar c
LEFT JOIN courses_enrolled ce
    ON ce.registered_on BETWEEN c.date AND (c.date + INTERVAL 1 DAY - INTERVAL 1 SECOND)
    AND ce.course_id = 2
GROUP BY c.date
ORDER BY c.date DESC;

    Or counting both at the same time –

    WITH RECURSIVE calendar (date) AS (
    SELECT '2022-11-13' # start date
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM calendar
    WHERE date + INTERVAL 1 DAY <= '2022-11-15' # end date
)
SELECT COUNT(ce.id) count_all, COUNT(IF(ce.course_id = 2, ce.id, NULL)) count_selected_course, c.date
FROM calendar c
LEFT JOIN courses_enrolled ce
    ON ce.registered_on BETWEEN c.date AND (c.date + INTERVAL 1 DAY - INTERVAL 1 SECOND)
GROUP BY c.date
ORDER BY c.date DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search