CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Makromat
Makromat

Reputation: 1572

group by is not working in postgreSQL

I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me.

This is working:

SELECT "id", "createdAt", "updatedAt" 
FROM "tables" AS "Table" 
WHERE "Table"."createdAt" 
BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04'
GROUP BY "id";

This is what I want but it is not working at all ...

SELECT "createdAt", "updatedAt" 
FROM "tables" AS "Table" 
WHERE "Table"."createdAt" 
BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' 
GROUP BY "createdAt";

I have error:

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

Upvotes: 0

Views: 5159

Answers (2)

Nuri Tasdemir
Nuri Tasdemir

Reputation: 9842

You can only select aggregate functions or the fields which listed in "GROUP BY" as in told 

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

Your first query only works because "id" is your primary key therefore it is guaranteed that each group will have only one row.

Upvotes: 0

AdamSkywalker
AdamSkywalker

Reputation: 11609

ID is unique and group by ID works just like a plain select.

Column createdAt is not unique and results with same createdAt value must be grouped. You should provide a way how they will be grouped - use aggreagete function, remove them from select clause or add them to group by statement.

Upvotes: 1

Related Questions