Search code examples
mysqlsqlgroup-bycountwindow-functions

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.


Solution

  • 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