"column must appear in the GROUP BY clause" when using date_trunc on timestamps

Question

I have an SQL query which counts contacts with given create date. Create date is stored as unix timestamp in miliseconds and I'm using to_timestamp method to format it correctly:

SELECT count(*) AS "Count", date_trunc('month', to_timestamp("contacts"."createdate"::double precision / 1000)) AS "Create Date"
FROM "contacts"
GROUP BY "contacts"."createdate"
ORDER BY "contacts"."createdate" ASC

Results are as following:

Count | Create Date
-----------------------------------------
1     | Sunday, February 1, 2015 12:00 AM
1     | Sunday, February 1, 2015 12:00 AM
1     | Wednesday, April 1, 2015 12:00 AM
1     | Wednesday, April 1, 2015 12:00 AM
1     | Wednesday, April 1, 2015 12:00 AM

I'd like to group them by month, so for the example above I would like to have:

Count | Create Date
-----------------------------------------
2     | February 2015
3     | April 2015

I change SQL to the following:

SELECT count(*) AS "Count", date_trunc('month', to_timestamp("contacts"."createdate"::double precision / 1000)) AS "Create Date"
FROM "contacts"
GROUP BY date_trunc('month', to_timestamp("contacts"."createdate"::double precision / 1000))
ORDER BY "contacts"."createdate" ASC

But receive an error:

ERROR: column "contacts.createdate" must appear in the GROUP BY clause or be used in an aggregate function

Is there any way I can do it properly?

Answer 1

You can refer to elements from your SELECT by number:

SELECT count(*) AS "Count", date_trunc('month', to_timestamp("contacts"."createdate"::double precision / 1000)) AS "Create Date"
FROM "contacts"
GROUP BY date_trunc('month', to_timestamp("contacts"."createdate"::double precision / 1000))
ORDER BY 2 ASC