Group By weeks between two dates-CodePudding

I have a table of logins and it has a created_at field that stores the current date it's something like

id, created_at
1, 2021-11-01
2, 2021-11-02
3, 2021-11-02
...
99, 2021-11-30

I have a range of dates that is 2021-11-01 to 2021-11-30 and I'd like to calculate the count of logins on week bases within that date range.

I have tried a lot in mysql but no luck, I can easily write multiple queries to achieve my result but was thinking if there is a way to do this in just one query.

Expected result:

count, date
3, 2021-11-01
4, 2021-11-08
46, 2021-11-15

I wrote a simple query but couldn't get how would I iterate to get weeks

SELECT count(id), CONCAT('Week ',WEEK('2021-11-30')) week
FROM logins

CodePudding user response：

Try a query like this:

SELECT
       count(id)
     , CONCAT('Week ',WEEK(l.date)) AS WEEK
     , CONCAT('Week ',MIN(l.date)) AS WEEKFROM

FROM logins l
WHERE l.date BETWEEN '2021-11-01' AND '2021-11-30'
GROUP BY WEEK(l.date)
ORDER BY WEEK(l.date);