MySql Query for running totals return same value for each years

Question

I am not expert in MySql, what I am trying to do is, to create a query will returns the running totals by month and years:

SELECT DISTINCT
    date_format(t.created_at, "%M") AS month,
    date_format(t.created_at, "%Y") AS year,
    SUM(COUNT(*)) over (order by MONTH(created_at) ) as cumulative
FROM team t
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY  MONTH(created_at) ASC

Query results: enter image description here

Anyone know how to solve this? :(

Answer 1

you can't use aggregate function container aggregate function, so you might try to use a subquery get count for each year, month, then use window function in the main query

SELECT  month,
        year,
        SUM(cnt) over (order by year,month) cumulative
FROM (
    SELECT MONTH(created_at) AS month,
        YEAR(created_at) AS year,
        COUNT(*) as cnt
    FROM team t
    GROUP BY YEAR(created_at), MONTH(created_at)
) t1