CODEWITHSUNDEEP
mysql
cosmosa
cosmosa

Reputation: 761

MySQL Group by dates within X number of days of each other

I have a mysql table with this schema:

id  |  date 
------------------------------------------
  1    2019-11-11    
  2    2019-11-10    
  3    2019-09-01
  4    2019-08-01

If the dates are within X number of days of each other, I want them grouped and the count of observations for each group calculated. For example setting X = 14 days, I want the output to be something like this:

id |  count
------------------------------------------
1  | 2
2  | 1
3  | 1
4  | 1

The reasoning is that there are 2 observations within 14 days of each other starting from 2019-11-11 and going back 14 days. For all other observations, there is just a single observation within 14 days of that observation.

Upvotes: 1

Views: 85

Answers (1)

Strawberry
Strawberry

Reputation: 33945

E.g.:

SELECT x.id
     , COUNT(y.id) total 
  FROM my_table x 
  JOIN my_table y 
    ON x.date BETWEEN y.date AND y.date + INTERVAL 14 DAY 
 GROUP 
    BY x.id;

Upvotes: 1

Related Questions