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?
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With