Postgresql – In SQL(postgresql) How to group based on a “timestamp without time zone” column

postgresql

I am trying to write some queries to group things based on each month in postgresql.

Say we have a table "crimes" which has 2 columns "activity date"(timestamp without time zone) and "zipcode"(character varying(5)), how to query the number of crimes for each month given a zipcode?

eg:
table "crimes":

activity date               zipcode
2014-11-22 00:52:00           12345
2014-10-22 00:52:00           12345
2014-10-24 00:52:00           12345
2014-12-22 00:52:00           54321

input: given zipcode"12345"

output: return

month        count
2014-10        2
2014-11        1

Best Solution

Try:

select
    extract(year from activity_date) as year,
    to_char(activity_date, 'Mon') as month,
    count(*) as count
from
    crimes
group by
    1,extract(month from activity_date);