group by month of unix timestamp field

Question

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)

score 57 · Accepted Answer · answered Mar 14 '12 at 09:27

57

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

answered Mar 14 '12 at 09:27

Eugen Rieck

64,175
10
70
92

would you be able to have a look at this - http://stackoverflow.com/questions/9699533/group-by-month-and-year-count-from-another-table – scarhand Mar 14 '12 at 10:07
Wow, I didn’t think this was possible. Kudos! – Rémi Breton May 29 '14 at 15:06
how can i get output with month name e.g month = JAN & sales =20 – Zaheer Ahmad Apr 03 '18 at 12:56

score 1 · Answer 2 · edited Jan 05 '18 at 15:31

1

try this query

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

edited Jan 05 '18 at 15:31

jasinth premkumar

1,430
1
12
22

answered Jan 05 '18 at 14:03

Sergeev Alexey

11
1

Can you please explain few words on how this query works – amarnath Jan 05 '18 at 14:22

score 1 · Answer 3 · answered Mar 14 '12 at 09:26

1

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)

answered Mar 14 '12 at 09:26

giorashc

13,691
3
35
71

This is not perfect solution of the problem but useful. – Mizbah Ahsan Oct 09 '13 at 15:47

Ben Y · Answer 4 · 2020-09-05T17:48:43.747

For people who would like to output a DATETIME rather than a month/year combo, here's another way to solve the problem. The benefit of using DATETIME is that it can easily be plugged into data visualization libraries and tools.

SELECT 
    LAST_DAY(FROM_UNIXTIME(date_assigned)), 
    COUNT(*)
FROM assignments
GROUP BY 1
ORDER BY 1 DESC

The LAST_DAY() function returns the last day of the month for a given DATE or DATETIME value. If you'd rather grab the first day, you could select this instead: ADDDATE(LAST_DAY(SUBDATE(FROM_UNIXTIME(date_assigned), INTERVAL 1 MONTH)), 1). It adds a day to the last date then subtracts a month.

The 1 values are column position integers -- shorthand so we don't have to type LAST_DAY(FROM_UNIXTIME(date_assigned)) any more than we need to (they start at 1, not 0).

Example output:

|-------------------------------------------|------------------|
| LAST_DAY(FROM_UNIXTIME(date_assigned))    | COUNT(*)         |
|-------------------------------------------|------------------|
| September 30, 2020, 12:00 AM              | 34               |
|-------------------------------------------|------------------|
| August 31, 2020, 12:00 AM                 | 23               |
|-------------------------------------------|------------------|

group by month of unix timestamp field

4 Answers4

Linked