Get data by week with sql and php

Question

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

If the current week is 6 I need to get the count from table `test` for the weeks `4,5,6,7,8` — Harea Costicla, Jun 07 '16 at 14:58

score 1 · Accepted Answer · answered Jun 07 '16 at 15:06

1

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.

answered Jun 07 '16 at 15:06

sagi

40,026
6
59
84

how to show `week 7 count 0` if for this week no data ? – Harea Costicla Jun 07 '16 at 15:52
That's a whole different question mate.. @hareacosticla – sagi Jun 07 '16 at 16:03
can you help me please ? – Harea Costicla Jun 07 '16 at 17:52
http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range . This is the same as your problem, follow the steps. @hareacpsticla – sagi Jun 07 '16 at 17:55
I have a problem with this sql..because if are data in multiple years...for example for week = 23 are data in database, then it's get data for week=23 for year 2015,2014,... – Harea Costicla Jun 08 '16 at 08:31
@HareaCosticla Then just filter those years, everything explained in the post, you can choose the dates you'd like – sagi Jun 08 '16 at 08:33

Get data by week with sql and php

1 Answers1