CODEWITHSUNDEEP
mysql
bhttoan
bhttoan

Reputation: 2736

Total number of users at end of each week for last 6 months

I am trying to find the total number of users at the end of each week for the last 26 weeks.

For example,

and so on

I can find the number of new users each week but how can I get it to show me the total (i.e. new this week + all those prior to this week) and so on without having to create 26 seperate selects? Is it possible?

I found Grouping total active users for each of the previous 8 weeks but it is for SQL Server and I don't think it does quite what I need.

I am using this to get the new each week:

select count(id) as total
    from users
    where join_date>='$sixmonths'
    group by WEEK(join_date)
    order by WEEK(join_date) desc
    limit 26

Upvotes: 1

Views: 494

Answers (1)

Justin
Justin

Reputation: 9724

Query:

select count(u.id) as total,
       (SELECT COUNT(u.id)
        FROM users u2
        WHERE WEEK(u2.join_date) <=
              WEEK(u.join_date)
        AND u2.id = u.id) AS Total_count
from users u
where u.join_date>='$sixmonths'
group by WEEK(u.join_date)
order by WEEK(u.join_date) desc
limit 26

For Query to work between years:

select count(u.id) as total,
       (SELECT COUNT(u.id)
        FROM users u2
        WHERE YEARWEEK(u2.join_date) <=
              YEARWEEK(u.join_date)
        AND u2.id = u.id) AS Total_count
from users u
where u.join_date>='$sixmonths'
group by YEARWEEK(u.join_date)
order by YEARWEEK(u.join_date) desc
limit 26

Upvotes: 1

Related Questions