Search code examples
mysqlselectcountunix-timestamp

group by month of unix timestamp field

I'm trying to get my code to output in the following format:

january 2012 - 34
february 2012 - 23

where 34 and 23 would be a count of the total rows that fall within that month that have the id_dealership of 7. I need this to output all data for every month that an assignment was ever made.

The assignments table structure is as follows:

id_dealer (int)
date_assigned (int)

I've tried this but it does not work at all:

SELECT MONTH(date_assigned), YEAR(date_assigned), COUNT(*)
FROM assignments
GROUP BY MONTH(date_assigned), YEAR(date_assigned)
Solution 
  • SELECT 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned)), 
  COUNT(*)
FROM assignments
GROUP BY 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned))