how to select Count of date ranges from mysql table

Question

I have a table:

Name         Registered Date
Amit         2017-01-01
Akshay       2017-01-03
Ankith       2017-01-05
Amit         2017-01-12
Amit         2017-01-13
Amit         2017-02-01
Amit         2017-02-01

I want to write a query which will display the registration weekly report: Say date between 2017-01-01 to 2017-03-01

Week          Count
2017-01-01    3
2017-01-08    2
2017-01-15    0
2017-01-22    0
2017-01-29    2
2017-02-05    0
2017-02-12    0
2017-02-19    0
2017-02-26    0

Here Count is the number of people who registered that week. 3 people registered in between 2017-01-01 to 2017-01-07. So which query i have to use for this result?

Thanks

Count of people who registered that week. 3 people registered in between 2017-01-01 to 2017-01-07 — sketchmypic s, Feb 17 '17 at 11:25
You have no PRIMARY KEY. This fact may prove problematic in due course. — Strawberry, Feb 17 '17 at 11:28
This example code is for reference. The table is pretty huge and I have to do a inner join as well. — sketchmypic s, Feb 17 '17 at 11:29

Asier Azkuenaga · Accepted Answer · 2017-02-17T12:06:11.933

0

If you can use the WEEK function and display the week number instead of a date, then:

select dummy.n, count(table.RegiseredDate)
from (SELECT 1 as n UNION SELECT 2 as n UNION SELECT 3 as n ... UNION SELECT 53 as n) dummy 
     left outer join table on dummy.n=WEEK(table.Registered Date)
where start_date>= x and end_date<= y
group by WEEK(Registered Date)

edited Feb 17 '17 at 12:06

answered Feb 17 '17 at 11:25

Asier Azkuenaga

1,199
6
17

Where do I input the start_date and end_date? – sketchmypic s Feb 17 '17 at 11:28
Thanks. Working with week number, count. Any way to get 0 count as well for the respective weeks? – sketchmypic s Feb 17 '17 at 11:37
I have edited the answer. I believe this time it works although it's not very elegant. Idea taken from [here](http://stackoverflow.com/questions/1475973/sql-select-to-get-the-first-n-positive-integers). – Asier Azkuenaga Feb 17 '17 at 12:08
Not sure if the same will work when x and y year is not same. Thanks a ton! – sketchmypic s Feb 17 '17 at 12:14
You are right. The answer will always show you every week of the year. – Asier Azkuenaga Feb 17 '17 at 12:18

how to select Count of date ranges from mysql table

1 Answers1