query mysql to find total value

please help me. imagine the table as bellow

id    name         date_join
1     joni        2020-01-01
2     dani        2020-01-01
3     fani        2020-01-02
4     rina        2020-01-02
5     budi        2020-01-02
6     john        2020-01-03
7     dita        2020-01-03
8     sani        2020-01-05
9     soni        2020-01-06
10    aple        2020-01-06
11    rita        2020-01-06
12    hari        2020-01-06

and I want to query and show result like this

dates            total_member
2020-01-01       2
2020-01-02       5
2020-01-03       7
2020-01-04       7
2020-01-05       8
2020-01-06       12
2020-01-07       12 

I dont know how to write query to show result like that. I used (date_join) as dates, count(id) and group by date(date_join) and the result not show like that.

Answer

In MySQL 8.0, you can solve this with aggregation and a window sum:

select
    date_join,
    sum(count(*))) over(order by date_join) total_members
from mytable
group by date_join
order by date_join

Or even without aggregation:

select date_join, total_members
from (
    select 
        date_join,
        count(*) over(order by date_join, name) total_members,
        row_number() over(order by date, name desc) rn
    from mytable
) t
where rn = 1
order by date_join

---

In earlier versions, one option is to select the distinct dates and use a correlated subquery to get the running count:

select 
    date_join,
    (select count(*) from mytable t1 where t1.date_join <= t.date_join) total_members
from (select distinct date_join from mytable) t