group by unique month per year, not just month

Question

Possible Duplicate:
MySQL Query GROUP BY day / month / year

I have the following mysql:

SELECT MONTH( FROM_UNIXTIME(  `timeStamp` ) ) as month , COUNT(  `id` )  as count
FROM  `discusComments` 
GROUP BY MONTH( FROM_UNIXTIME(  `timeStamp` ) ) 
ORDER BY  MONTH( FROM_UNIXTIME(  `timeStamp` ) ) ASC 
LIMIT 15

It gets the amount of entries made per month in the past 15 months. I was wondering WHY it only displayed the past 12 months ... then I realised the count was an aggregate of all years and not unique per month year. So the value for december could be 2012 and 2011 together.

I donÄt want this. I want to get the past 15 months and the amount of entries made for UNIQUE month year, e.g. december 2012, november 2012 etc.

I dont know how to make it so that it groups by "month year" and not just "month" or "year" — Jake, Dec 16 '12 at 20:57

Shiplu Mokaddim · Answer 1 · 2012-12-16T21:00:53.933

1

Add year to your SELECT column list and add the alias to GROUP BY too.

SELECT YEAR(FROM_UNIXTIME(`timestamp`))  AS year, 
       MONTH(FROM_UNIXTIME(`timestamp`)) AS month, 
       COUNT(`id`)                       AS count 
FROM   `discuscomments` 
GROUP  BY year, 
          month 
ORDER  BY year,
          month
LIMIT  15

edited Dec 16 '12 at 21:00

answered Dec 16 '12 at 20:54

Shiplu Mokaddim

56,364
17
141
187

score 1 · Answer 2 · answered Dec 16 '12 at 20:55

1

The most straight forward idea I have on this is normally to change the format of the date value to a unique and speaking string, like 2012-12 for December 2012 and 2011-10 for October 2011 etc.

A function you can use for that is DATE_FORMAT():

 DATE_FORMAT(FROM_UNIXTIME(timeStamp), '%Y-%m')

These strings are then easily sortable, e.g. ASC:

Example SQL query:

SELECT
  DATE_FORMAT(FROM_UNIXTIME(timeStamp), '%Y-%m') as month, 
  COUNT(id) as count
FROM  discusComments
GROUP BY month
ORDER BY month ASC 
LIMIT 15

answered Dec 16 '12 at 20:55

hakre

193,403
52
435
836

But this adds complexity when later you have to split that `month` column. – Shiplu Mokaddim Dec 16 '12 at 21:02
Yes, it's just some kind of "do-it-yourself"/"help-yourself" solution that is applicable even on low-level systems. Your answer is much nicer adopting to SQL itself. Also just seeing [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) – hakre Dec 16 '12 at 21:51

score 0 · Answer 3 · answered Dec 16 '12 at 21:01

0

Try this

months_between(to_date ('2009/05/15', 'yyyy/mm/dd'), 
                     to_date ('2009/04/16', 'yyyy/mm/dd'))

answered Dec 16 '12 at 21:01

Ehsan

9
1
6

1

Hey you should probably elaborate as to why your solution would work – brenjt Dec 16 '12 at 21:20

group by unique month per year, not just month

3 Answers3