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.

In your second subquery, you have a condition "ed3.event_date > ed3.event_date", this can't ever be true. You could use CTE rather than subquery and then access the correct result set to compare. — Jonas Metzler, Jul 24 '23 at 11:02
You're Fiddle is MySql 5.6 - is that your *actual* version or only because the Fiddle has no later version? — Stu, Jul 24 '23 at 11:11
10.3.25 is SQL version, sorry for not adding that to my question. — TheCarver, Jul 24 '23 at 11:27
I have corrected your tags. You also have a start time but don't appear to use this, except for your final ordering, which is possibly misleading. should you also not be using this to count the number of future events? — Stu, Jul 24 '23 at 11:46

score 2 · Accepted Answer · answered Jul 24 '23 at 11:54

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

Thanks!! I tried SelVazi's answer but it seemed that the counts were based only on that month and year being searched, and not all time. The sub query you added was perfect for the no_next_in_series count but had to change the first sub query back to my original one as, again, it was based on the month being searched and not an all time count, but that wasn't your doing. Thanks for the help. — TheCarver, Jul 24 '23 at 12:18
It wasn't obvious whether it should or should not, but you have a solution so that's good! — Stu, Jul 24 '23 at 12:20

SelVazi · Answer 2 · 2023-07-24T12:28:33.173

1

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

edited Jul 24 '23 at 12:28

answered Jul 24 '23 at 11:05

SelVazi

10,028
2
13
29

Thank you for taking the time to help me. Please see my comment on the accepted answer as to why I chose it over yours. Cheers. – TheCarver Jul 24 '23 at 12:19

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

2 Answers2