CODEWITHSUNDEEP
mysql
Murat Kaya
Murat Kaya

Reputation: 1321

Mysql Select Query group by 7 Days Interval

I'm trying to group my data by 7 days interval.

for example.

I have a data which is you can find it below.

count   startDate          finish_date         
1247    2017-03-09 08:43:18 2017-03-09 16:05:34
1681    2017-03-10 08:30:13 2017-03-10 16:31:55
1464    2017-03-11 08:36:50 2017-03-11 16:42:03
1343    2017-03-12 08:26:57 2017-03-12 16:39:58
1333    2017-03-13 08:35:34 2017-03-13 16:26:18
1215    2017-03-14 08:36:58 2017-03-14 16:13:20
1817    2017-03-16 08:24:49 2017-03-16 17:18:19
1675    2017-03-17 08:22:30 2017-03-17 16:36:58
1546    2017-03-18 08:33:52 2017-03-18 16:51:52
1443    2017-03-20 08:11:00 2017-03-20 16:26:38
1481    2017-03-21 08:26:04 2017-03-21 16:57:30
1574    2017-03-23 08:19:07 2017-03-23 16:12:46
1270    2017-03-24 08:25:25 2017-03-24 16:37:59
1765    2017-03-25 08:22:58 2017-03-25 16:44:24
1200    2017-03-26 08:37:47 2017-03-26 14:59:51
1479    2017-03-27 08:17:50 2017-03-27 15:18:32

And I wanted to group them by 7 days interval. I tried this. for it.

select count(*), min(locationDate) as startDate, max(locationDate) as finish_date from location where tagCode = 24901 and xLocation >= 278 and xLocation <= 354 and yLocation >= 239 and yLocation <= 426 and locationDate
>= DATE_SUB('2017-03-01 00:00:01',INTERVAL 7 day) and locationDate <= '2017-03-27 23:59:59' group by DATEDIFF(locationDate, '2017-03-01 00:00:01') div 7

And data is like.

count   startDate          finish_date         
8283    2017-03-09 08:43:18 2017-03-14 16:13:20
7962    2017-03-16 08:24:49 2017-03-21 16:57:30
7291    2017-03-23 08:19:07 2017-03-27 15:22:05

Problem is Second Week it must start from 2017-03-15 and third week need to start 2017-03-22 but because of there is no data in on days its not starting how can I fix it ?

Upvotes: 1

Views: 1625

Answers (3)

Daantje
Daantje

Reputation: 2486

I just use GROUP BY DATE_FORMAT: 

SELECT someTimeStamp,SUM(amount) AS Total FROM sometable WHERE 1 GROUP BY DATE_FORMAT(someTimeStamp,"%Y%v")

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5926

As I asked you in my comment, I think the result you wrote would be good with the input you provided, but it wouldn't be with a different input (like having 2017-03-15 but not 2017-03-16).

A solution could be to write the query kind of like this

select  sum(count) as count, min(location_date), max(location_date)
from    (
            select  t1.location_date,
                    t1.count,
                    date_sub(location_date, interval (datediff(t1.location_date, t2.min_date) % 7) day) week_start
            from    location t1
            cross join
                    (select min(location_date) as min_date from location) t2
            where   t1.tagCode = 24901 and
                    t1.xLocation between 278 and 354 and
                    t1.yLocation between 239 and 426 and
                    t1.locationDate >= DATE_SUB('2017-03-01 00:00:01',INTERVAL 7 day) and
                    t1.locationDate <= '2017-03-27 23:59:59'
                    ) t3
group by week_start

I tested a simplified version of this on a simplified version of your input, there might be typos...

Edit

To display both interval starting date and ending date, try with this

select  sum(count) as count, week_start, week_end
from    (
            select  t1.count,
                    date_sub(location_date, interval (datediff(t1.location_date, t2.min_date) % 7) day) week_start,
                    date_sub(location_date, interval (datediff(t1.location_date, t2.min_date) % 7) - 6 day) week_end
            from    location t1
            cross join
                    (select min(location_date) as min_date from location) t2
            where   t1.tagCode = 24901 and
                    t1.xLocation between 278 and 354 and
                    t1.yLocation between 239 and 426 and
                    t1.locationDate >= DATE_SUB('2017-03-01 00:00:01',INTERVAL 7 day) and
                    t1.locationDate <= '2017-03-27 23:59:59'
                    ) t3
group by week_start, week_end

Upvotes: 1

Charif DZ
Charif DZ

Reputation: 14731

I think you can do that : you need to change the result of your query from this :

1   1247    2017-03-09 08:43:18 2017-03-09 16:05:34
2   1681    2017-03-10 08:30:13 2017-03-10 16:31:55
3   1464    2017-03-11 08:36:50 2017-03-11 16:42:03
4   1343    2017-03-12 08:26:57 2017-03-12 16:39:58
5   1333    2017-03-13 08:35:34 2017-03-13 16:26:18
6   1215    2017-03-14 08:36:58 2017-03-14 16:13:20
7   1817    2017-03-16 08:24:49 2017-03-16 17:18:19
8   1675    2017-03-17 08:22:30 2017-03-17 16:36:58
9   1546    2017-03-18 08:33:52 2017-03-18 16:51:52
10  1443    2017-03-20 08:11:00 2017-03-20 16:26:38
11  1481    2017-03-21 08:26:04 2017-03-21 16:57:30
12  1574    2017-03-23 08:19:07 2017-03-23 16:12:46
13  1270    2017-03-24 08:25:25 2017-03-24 16:37:59
14  1765    2017-03-25 08:22:58 2017-03-25 16:44:24
15  1200    2017-03-26 08:37:47 2017-03-26 14:59:51
16  1479    2017-03-27 08:17:50 2017-03-27 15:18:32

to This using the logic of computing the number of days between the max date and the min date of the first line :

           --  max date of the row          min date of the first row
select FLOOR(datediff('2017-03-12 16:05:34', '2017-03-09 08:43:18')/7);

select  FLOOR( datediff('2017-03-16 17:18:19', '2017-03-09 08:43:18')/7);

 -- what is important that you always compute the max date - the min date of the first row the same row like in your example is : '2017-03-09 08:43:18'
select  FLOOR( datediff(max_date, '2017-03-09 08:43:18')/7);

    rec_sum     min_date            max_date            day_diff
1   1247    2017-03-09 08:43:18 2017-03-09 16:05:34   0
2   1681    2017-03-10 08:30:13 2017-03-10 16:31:55   0
3   1464    2017-03-11 08:36:50 2017-03-11 16:42:03   0
4   1343    2017-03-12 08:26:57 2017-03-12 16:39:58   0
5   1333    2017-03-13 08:35:34 2017-03-13 16:26:18   0
6   1215    2017-03-14 08:36:58 2017-03-14 16:13:20   0
7   1817    2017-03-16 08:24:49 2017-03-16 17:18:19   1
8   1675    2017-03-17 08:22:30 2017-03-17 16:36:58   1
9   1546    2017-03-18 08:33:52 2017-03-18 16:51:52   1
10  1443    2017-03-20 08:11:00 2017-03-20 16:26:38   1
11  1481    2017-03-21 08:26:04 2017-03-21 16:57:30   1
12  1574    2017-03-23 08:19:07 2017-03-23 16:12:46   2
13  1270    2017-03-24 08:25:25 2017-03-24 16:37:59   2
14  1765    2017-03-25 08:22:58 2017-03-25 16:44:24   2
15  1200    2017-03-26 08:37:47 2017-03-26 14:59:51   2
16  1479    2017-03-27 08:17:50 2017-03-27 15:18:32   2

-- now you can group the new result by the new field the result of division.

select 
      sum(rec_sum) ,
      min(min_date),
      max(max_date)
from (query result in the previous list)
group by day_diff 

i know it's a little bit hard but i think you can do it the hard way is the day_diff computing .

Upvotes: 0

Related Questions