按月的unix时间戳字段分组 [英] 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

其中34和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.

作业表结构如下：

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)

推荐答案

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

这篇关于按月的unix时间戳字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！