6

I'm using MySQL and I have the following table:

| clicks | int  |
|   period  | date |

I want to be able to generate reports like this, where periods are done in the last 4 weeks:

|   period    | clicks |
|  1/7 - 7/5  |  1000  | 
| 25/6 - 31/7 |  ....  |
| 18/6 - 24/6 |  ....  |
| 12/6 - 18/6 |  ....  |

or in the last 3 months: 

| period | clicks |
|  July  |  ....  |
|  June  |  ....  |
| April  |  ....  |

Any ideas how to make select queries that can generate the equivalent date range and clicks count?

Tomas
  • 57,621
  • 49
  • 238
  • 373
khelll
  • 23,590
  • 15
  • 91
  • 109

2 Answers2

14
SELECT
 WEEKOFYEAR(`date`) AS period,
 SUM(clicks) AS clicks
FROM `tablename`
WHERE `date` >= CURDATE() - INTERVAL 4 WEEK
GROUP BY period

SELECT
 MONTH(`date`) AS period,
 SUM(clicks) AS clicks
FROM `tablename`
WHERE `date` >= CURDATE() - INTERVAL 3 MONTH
GROUP BY period
David S
  • 105
  • 1
  • 1
  • 10
simendsjo
  • 4,739
  • 2
  • 25
  • 53
  • That's a better check for the period, I corrected my answer to reflect it. – Keeper Jun 10 '10 at 09:08
  • Out of curiosity, how the queries should be if the period field is an integer like 20100609 which corresponds to 2010-06-09 date. Would the performance be better in such int type? – khelll Jun 10 '10 at 09:50
  • Probably only if you need to filter a range of dates because you can't filter by week (you still have to cast it as a date). And remember to choose an answer :P – Keeper Jun 10 '10 at 10:19
  • @khell: I wouldn't bother with such micro-optimalizations. I've done this a thousand times on large tables without any problems. Just make sure you are indexing the column. – simendsjo Jun 10 '10 at 14:12
  • I'm wondering if there is a way to the display the periods for the weekly report as mentioned in the question – khelll Jun 10 '10 at 18:26
  • I'll add that if you're going to be grouping on a function like `WEEKOFYEAR(date)`, you may also want to group by `YEAR(date)` because otherwise week 3 2015 and week 3 2016 would be grouped into a single data point (week 3), which may not be your expected behavior. – Anton Sep 16 '16 at 12:34
  • @simendsjo - how can we use date range `where date >= staringdate and date <= endingdate - interval 3 month` ? – Suleman khan Aug 04 '17 at 14:42
3

For the last 3 months you can use:

SELECT MONTH(PERIOD), SUM(CLICKS)
FROM TABLE
WHERE PERIOD >= NOW() - INTERVAL 3 MONTH
GROUP BY MONTH(PERIOD)

or for the last 4 weeks:

SELECT WEEK(PERIOD), SUM(CLICKS)
FROM TABLE
WHERE PERIOD >= NOW() - INTERVAL 4 WEEK
GROUP BY WEEK(PERIOD)

Code not tested.

Keeper
  • 3,516
  • 1
  • 18
  • 29
  • I don't know if the optimizer works with WEEK(Period) without checking all the dates in the result. That's why I like to use WEEK(Period) AS WeekPeriod, Group by WeekPeriod – simendsjo Jun 10 '10 at 09:31
  • I use mainly Oracle for which you have to group by the same field you select (you can't alias a field and group by that). I think internally the query is the same. – Keeper Jun 10 '10 at 10:17