Harea Costicla Harea Costicla - 6 months ago 11
MySQL Question

Get data by week with sql and php

I have a problem and I have no idea how to do that. Please help me with an idea. So I have a table :

test
, with columns :

id begin end
1 2016-06-06 15:30:30 2016-06-08 16:40:40


Now I need to get the count of id by week
The result need to be :
For example if this week is the week number 6 I need to get :

Week count
4 10
5 10
6 12
7 19
8 27


Thx four you help

Answer

Well, what happens if the begin is week 4, and end is week 5? which week is calculated?

Anyway, if I understood you, you want two previous weeks and 2 future weeks :

SELECT s.* FROM (
    SELECT YEAR(`start`) as year_col,WEEK(`start`) as week_col,count(*)
    FROM YourTable t
    GROUP BY year_col,week_col) s
WHERE s.week_col between WEEK(now())-2 and WEEK(now())+2

I've also added year into the consideration, since week 4 from 2015 will be group together with week 4 from 2016. If its not possible, exclude it from the query.