group by month of unix timestamp field

mysql select count unix-timestamp
scarhand picture scarhand · Mar 14, 2012 · Viewed 25.2k times · Source

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)

Answer

Eugen Rieck picture Eugen Rieck · Mar 14, 2012 
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))

Related questions

MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
Create a temporary table in a SELECT statement without a separate CREATE TABLE

Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I …

Read More