countmariadbaccumulate

MariaDB / MySQL: Partial "total" number of rows for moving time-window

I have a MariaDB Database with Users and their appropriate registration date, something like:

+----+----------+----------------------+
| ID | Username | RegistrationDatetime |
+----+----------+----------------------+
|  1 | A        |  2022-01-03 12:00:00 |
|  2 | B        |  2022-01-03 14:00:00 |
|  3 | C        |  2022-01-04 23:00:00 |
|  4 | D        |  2022-01-04 14:00:00 |
|  5 | E        |  2022-01-05 14:00:00 |
+----+----------+----------------------+

I want to know the total number of users in the system at the end of every date with just one query - is that possible?

So the result should be something like:

+------------+-------+
|   Date     | Count |
+------------+-------+
| 2022-01-03 |     2 |
| 2022-01-04 |     4 |
| 2022-01-05 |     5 |
+------------+-------+

Yes it's easy with single queries and looping over the dates using PHP, but how to do it with just one query?

EDIT Thanks for all the replies, yes, users could get cancelled / deleted, i.e. going by the max(ID) for a specific time period is NOT possible. There could be gaps in the column ID

Solution

  • Use COUNT() window function:

    SELECT DISTINCT 
       DATE(RegistrationDatetime) AS Date,
       COUNT(*) OVER (ORDER BY DATE(RegistrationDatetime)) AS Count
FROM tablename;

    See the demo.