Splitting month into weeks and using GROUP BY

Question

This is a question, which I could not find answer to anywhere. Okay. here it is. I have two date ranges (This month and the last month)

Last month - 01/01/2015 (January 1 2015) to 31/01/2015 This month - 01/02/2015 (1st Feb 2015) to 28/02/2015

Now, each month has weeks. I have a table with column created_at. I want to fetch all the records week-wise into an array (to plot a graph) with their corresponding sum(value) or count(value) .

So it will be something like this: Last Month: Week 1 - 25 Week 2 - 34 etc.

This Month: Week 1: 55 Week 2: 56 etc.

The date is in this format in created_at: 2015-07-21 01:27:14 (Y-m-d H:i:s)

Please post the data structure and show, what you've tried so far. — sec_aw, Nov 19 '15 at 08:59
Do you ALWAYS have data for a particular week? Do you need to output a row with a sum and count of 0 for a week with no records? — Kickstart, Nov 19 '15 at 10:40

genespos · Accepted Answer · 2015-11-19T11:25:27.367

4

In MySql You can use WEEK() to get the number of the week (from 1 to 53)

O you can use WEEKDAY() or DAYOFWEEK() the first bigins on Monday the second on Sunday.

You can use them into a GROUP BY with HAVING

Something like:

SELECT count(*)
FROM `YourTable` 
WHERE `created_at` >= '2015-10-01' AND `created_at`< '2015-11-01'
GROUP BY WEEK(`created_at`)

To use the workaround you found You need to do something similar:

create a table named "numbers" with a field "id" (autoincrement) and 31 rows (one for each day of a month)

Then use a query like this:

SELECT count(i.created_at)
FROM
(SELECT DATE_FORMAT(DATE_ADD('2015-12-01', INTERVAL -n.id DAY), '%Y-%m-%d') AS AllDays
             FROM numbers n) AS DaysOfMonth
Left Join
YourTableName i
ON STR_TO_DATE(i.created_at, '%Y-%m-%d') = DaysOfMonth.AllDays
GROUP BY WEEK(AllDays)

(try to adapt it to your needs)

edited Nov 19 '15 at 11:25

answered Nov 19 '15 at 09:06

genespos

3,211
6
38
70

Can we modify it so that 0 or null is returned where count(*) is zero for that week? – kirobo Nov 19 '15 at 09:40
@kirobo I'm not sure, but I think you need a stored procedure with a loop on every week to do what you ask. Otherwise you can use `UNION` and repeat the query for every week or use `php` to extract every week – genespos Nov 19 '15 at 10:09
I can't figure it out. This is so simple. I feel so stupid. http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range/3538926#3538926 Says I need to create a table, WHY? This is such a simple task, why would I need to create a table? – kirobo Nov 19 '15 at 10:20
@kirobo very nice workaround :). The table you need to create makes you able to get results for each week (even there aren't records in the table that you are using) by joining them – genespos Nov 19 '15 at 10:28
@kirobo I've added some hint for you – genespos Nov 19 '15 at 11:25

score 1 · Answer 2 · answered Nov 19 '15 at 09:08

1

What you need to do is group by the week and then sum the values. Here's a simple example of how it might work:

SELECT DATE_FORMAT(created_at,'%Y-%V') as interval, SUM(units_sold) as total_sold
FROM sales 
GROUP BY DATE_FORMAT(created_at,'%Y-%V')

What you'll be getting is the year ant week number (ex. 2015-50) and the sum from that interval.

A table like this:

+----+------------+---------------------+
| id | units_sold |     created_at      |
+----+------------+---------------------+
|  1 |          2 | 2015-01-01 09:00:00 |
|  2 |          4 | 2015-01-04 10:00:00 |
|  3 |          1 | 2015-01-12 12:00:00 |
|  4 |          4 | 2015-01-16 13:00:00 |
+----+------------+---------------------+

Would result to:

+----------+------------+
| interval | total_sold |
+----------+------------+
| 2015-01  |          6 |
| 2015-03  |          5 |
+----------+------------+

answered Nov 19 '15 at 09:08

Andrius

5,934
20
28

SELECT DATE_FORMAT(created_at,'%Y-%V') AS intrvl, COUNT(DISTINCT id) AS records FROM users GROUP BY DATE_FORMAT(created_at,'%Y-%V') It gives me year and week number and total in the respective week of year, I want to find all the new registrations made (count (distinct id) as newrecords) between start of month and end of month. – kirobo Nov 19 '15 at 09:20
For example, using PHP, $nmFrom = date("Y-m-d H:i:s",strtotime("first day of last month")); $nmTo = date("Y-m-d H:i:s",strtotime("last day of last month")); – kirobo Nov 19 '15 at 09:20
You can just use a different grouping then. Try using `%Y-%m`, it will group by the month. – Andrius Nov 19 '15 at 09:21
I need it specifically by weeks. – kirobo Nov 19 '15 at 09:22
If you need it by weeks, you can use what I posted. – Andrius Nov 19 '15 at 09:23
That's giving me for the specific year (created_at) whereas I require between two dates, I think I'm almost there, I keep getting errors. How o modify this to get COUNT () between two dates - say start and end of month. – kirobo Nov 19 '15 at 09:25
`%Y-%m` will group the `created_at` fields by the month name so you will get the `COUNT()` of each month. – Andrius Nov 19 '15 at 09:26

score 0 · Answer 3 · edited Nov 19 '15 at 09:15

0

I think it is useful for you...

SELECT GROUP_CONCAT(id), COUNT(id) AS idcount,SUM(id) AS idsum,
MONTHNAME(order_created_date) AS month_name, WEEK(order_created_date) 
AS weeks  FROM orders GROUP BY WEEK(order_created_date)

edited Nov 19 '15 at 09:15

Narendrasingh Sisodia

21,247
6
47
54

answered Nov 19 '15 at 09:09

MaThar Beevi

294
1
2
10

Splitting month into weeks and using GROUP BY

3 Answers3