在时间戳上使用date_trunc时，“列必须出现在GROUP BY子句中”
在时间戳上使用date_trunc时，“列必须出现在GROUP BY子句中”
提问于 2018-01-09 23:46:41
我有一个SQL查询，它计算具有给定创建日期的联系人。Create date以毫秒为单位存储为unix时间戳，我使用to_timestamp方法将其正确格式化：
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结果如下：
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我想按月对它们进行分组，因此对于上面的示例，我希望有：
Count | Create Date
-----------------------------------------
2 | February 2015
3 | April 2015我将SQL更改为以下内容：
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但是收到一个错误：
ERROR: column "contacts.createdate" must appear in the GROUP BY clause or be used in an aggregate function有什么办法可以让我做得很好吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-01-09 23:57:24
您可以通过编号引用选择中的元素：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48171880
复制相关文章
相似问题
腾讯云开发者
