Creating sub query to count how many rows follow current row by date

Question

I have a table of events and a table of event dates.

For each row returned from the database, I would like to show the total number of dates for that event and to show the total number of dates remaining after that event.

For example, if 'Event 1' had three different dates on 29/05/2023, 30/05/2023 and 31/05/2023, I would like to have the following data:

Event 1 | 29/05/2023 | 3 shows in total | 2 remaining after this
Event 1 | 30/05/2023 | 3 shows in total | 1 remaining after this
Event 1 | 31/05/2023 | 3 shows in total | 0 remaining after this

I believe I need a sub query and so I tried the following query:

SELECT
    e.id AS event_id,
    e.title AS event_title,
    ed.event_date,
    ed.event_start,
    (
      SELECT COUNT(*)
      FROM event_dates AS ed2
      WHERE ed2.event_id = e.id
    ) AS no_in_series,
    (
      SELECT COUNT(*)
      FROM event_dates AS ed3
      WHERE ed3.event_id = e.id AND ed3.event_date > ed3.event_date
    ) AS no_next_in_series
FROM event_dates AS ed
LEFT JOIN events AS e ON ed.event_id = e.id
WHERE DAY(ed.event_date) = '05' AND MONTH(ed.event_date) = '07' AND YEAR(ed.event_date) = '2023'
ORDER BY ed.event_start ASC;

The first sub query count to count the total number of shows works just fine but the second sub query to count how many events follow the current date/row, does not.

Please see my SQL Fiddle here for data and last attempt, and help if you can.

I'm using MariaDB version 10.3.25.

Answer 1

I think you'd be best off using a combination of window function to count the totals and a correlated query for the future count. The below assumes it makes sense to also include the event_time, if that's not a requirement then simply remove accordingly.

select ed.event_id, e.title, ed.event_date, ed.event_start, 
    Count(*) over(partition by event_id) No_in_series, 
    (
      select Count(*) from event_dates ed2 
      where ed2.event_id = ed.event_id 
        and timestamp(ed2.event_date, ed2.event_start) 
          > timestamp(ed.event_date, ed.event_start)
    ) No_next_in_series
from event_dates ed
join events e on e.id = ed.event_id
where month(ed.event_date) = '07' and year(ed.event_date) = '2023'
order by ed.event_id, ed.event_date;

See a mariadb fiddle

Answer 2

You can do it by using the window functions count() to count total and rank() to get no_next_in_series :

SELECT
    e.id AS event_id,
    e.title AS event_title,
    ed.event_date,
    ed.event_start,
    count(*) over (partition by event_id) AS no_in_series,
    rank() over (partition by event_id order by timestamp(event_date, event_start) desc) - 1 AS no_next_in_series
FROM event_dates AS ed
LEFT JOIN events AS e ON ed.event_id = e.id
WHERE MONTH(ed.event_date) = '07' AND YEAR(ed.event_date) = '2023'
ORDER BY ed.event_date ASC;

Demo here