CODEWITHSUNDEEP
mysqlsql
MikeC
MikeC

Reputation: 480

MYSQL group by month but not on first day of month

My table votes contains votes that have been made by users at different times:

id  item_id position    user_id created_at
1   2   0   1   11/21/2013 11:27
26  1   1   1   11/21/2013 11:27
27  3   2   1   11/21/2013 11:27
42  2   2   1   12/7/2013 2:20
41  3   1   1   12/7/2013 2:20
40  1   0   1   12/7/2013 2:20
67  2   2   1   12/13/2013 1:13
68  1   1   1   12/13/2013 1:13
69  3   0   1   12/13/2013 1:13
84  2   0   1   12/28/2013 2:29
83  3   2   1   12/28/2013 2:29
82  1   1   1   12/28/2013 2:29
113 3   0   1   1/17/2014 22:08
114 1   1   1   1/17/2014 22:08
115 2   2   1   1/17/2014 22:08
138 2   0   1   1/20/2014 16:49
139 1   1   1   1/20/2014 16:49
140 3   2   1   1/20/2014 16:49
141 1   1   11  1/20/2014 16:51
142 3   2   11  1/20/2014 16:51
143 2   0   11  1/20/2014 16:51

I need to tally the results on a monthly basis but here's the tricky part: the start/end of the month does not necessarily fall on the first day of the month. So if the votes are due on the 10th day of every month, I need a vote that was cast on the 10th to be in a different group from a vote that was cast on the 11th. Using the data above, I want to get three groups:

Group 1: 6 votes (11/21 and 12/7) Group 2: 6 votes (12/13, 12/28) Group 3: 9 votes (1/17, 1/20)

I've tried a lot of approaches but to no avail. This is my query right now:

select created_at, ADDDATE(DATE_FORMAT(created_at, '%Y-%m-01'),interval 10 day) as duedate,count("id") from votes where list_id = 2 group by duedate

I am getting group sizes of 3, 9, and 9, not 6, 6 and 9. Any help you can provide would be much appreciated. Thanks.

Upvotes: 1

Views: 936

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your query is close. You just need to subtract 9 days (10 - 1) from the current day to get the month:

select created_at, date_sub(created_at, interval 9 day) as duedate,
       count(id)
from votes
where list_id = 2
group by duedate;

date_format() converts a date to a string. There is no need to convert a date value to a character value for this query.

EDIT:

To group by month:

select date_format(date_sub(created_at, interval 9 day), '%Y-%m') as YYYYMM,
       count(id)
from votes
where list_id = 2
group by YYYYMM;

Upvotes: 1

Related Questions