Harea Costicla Harea Costicla - 1 year ago 67
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 :

, 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 Source

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 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download