Counting number of rows between 2 dates and 1 where clause-CodePudding

I've a mysql table with me

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 where course id = 2

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

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;

CodePudding user response：

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.

CodePudding user response：

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;