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)

回答1:

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))

回答2:

Your date_assigned column should be of type DATE. AFAIK MONTH works on date columns and if you want the month name from a DATE column use : MONTHNAME(date_assigned)

回答3:

try this query

SELECT 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned)), 
  COUNT(*)
FROM assignments
GROUP BY 1,2