MySQL group by 7 days for current quarter

Question

Please tell how to group by 7 days for current quarter without using nested query. It should be like :

Week_period Date                      Clicks

1           1-10-2016 To 07-10-2016   30
2           8-10-2016 To 14-10-2016   40
3           15-10-2016 To 28-10-2016   20
4           29-10-2016 To 04-11-2016   10
5           05-11-2016 To 11-11-2016   80
6           12-11-2016 To 18-11-2016   90

And will continue till 31-12-2016

CREATE TABLE IF NOT EXISTS `user_click` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Thanks for the help.. I modified the code as listed below :

SELECT STR_TO_DATE(CONCAT(YEARWEEK(`date`), ' Sunday'), '%X%V %W')   week_start, 
       STR_TO_DATE(CONCAT(YEARWEEK(`date`), ' Saturday'), '%X%V %W') week_end, 
       SUM(id) clicks 
FROM   `user_click`  where QUARTER(`date`) = QUARTER(CURRENT_DATE())
GROUP  BY YEARWEEK(`date`)

But it gives a date from september as well which is not right. Currently I am getting output as :

 week_start     week_end    clicks  
2016-09-25  2016-10-01  1
2016-10-02  2016-10-08  70

But the result required does not matches as i want result depend upon the days ie 1st october to 7th october as described earlier. Please help regarding this regard

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 13 '16 at 17:47

score 0 · Answer 1 · answered Dec 07 '16 at 09:13

0

I would try to group by YEARWEEK(date) so something like this:

  SELECT YEARWEEK("date"), count("id")
    FROM "user_click"
GROUP BY YEARWEEK("date");

You might need to pimp the output a little but this should do the trick.

answered Dec 07 '16 at 09:13

frlan

6,950
3
31
72

score 0 · Answer 2 · answered Dec 07 '16 at 09:42

0

You can use YEARWEEK to group the 7 days, additionally, you can use STR_TO_DATE to get the start and the end of the week. You will probably need to sum the clicks as well

SELECT STR_TO_DATE(CONCAT(YEARWEEK(`date`), ' Sunday'), '%X%V %W')   week_start, 
       STR_TO_DATE(CONCAT(YEARWEEK(`date`), ' Saturday'), '%X%V %W') week_end, 
       SUM(clicks) clicks 
FROM   `user_click` 
WHERE  `date` BETWEEN '2016-10-01' AND '2016-12-31' 
GROUP  BY YEARWEEK(`date`)

answered Dec 07 '16 at 09:42

phobia82

1,257
8
10

try the solution mentioned here: http://stackoverflow.com/questions/14379337/group-rows-by-7-days-interval-starting-from-a-certain-date – phobia82 Dec 07 '16 at 10:24

score -1 · Answer 3 · answered Dec 07 '16 at 10:43

I Solved the same with using below query. Thanks for your time.

SELECT 1 + DATEDIFF(date, MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL    1 QUARTER) DIV 7  AS weekNumber, MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL    1 QUARTER + INTERVAL (DATEDIFF(date, MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL    1 QUARTER) DIV 7) WEEK AS week_start_date, count(id)
    FROM 
        user_click 
    WHERE 
        QUARTER(date) = QUARTER(CURRENT_DATE()) AND date >= (MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL 1 QUARTER)
    GROUP BY
        DATEDIFF(date, MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL 1 QUARTER) DIV 7 ;

MySQL group by 7 days for current quarter

3 Answers3