CODEWITHSUNDEEP
sqlpostgresqlgroup-byhaving
leofontes
leofontes

Reputation: 927

Postgres SQL query doesn't identify the column

I'm having a huge problem with my query, for some reason I just can't get one of the WHERE clauses to work.

This is my SQL:

SELECT COUNT(*) FROM "diets" JOIN "meals" on "idDiet" = "dietId" 
WHERE kcal != 0 AND "diets.createdAt" > '2016-10-2' 
GROUP BY "userIdUser" HAVING count(*) >= 5;

And my error:

ERROR:  column "diets.createdAt" does not exist

My scheme for both tables:

enter image description here enter image description here

Any idea on what I must do for this query to work? Thank you very much, if more information is needed please let me know.

Upvotes: 1

Views: 124

Answers (2)

this.hart
this.hart

Reputation: 328

 SELECT COUNT(*) FROM diets a  JOIN  meals b on a.idDiet = b.dietId 
 WHERE a.kcal <> 0 AND a.createdAt > '2016-10-2' 
 GROUP BY a.userIdUser HAVING count(*) >= 5;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270783

Your quotes are wrong:

SELECT COUNT(*)
FROM "diets" JOIN
     "meals" 
     ON "idDiet" = "dietId" 
WHERE kcal <> 0 AND "diets"."createdAt" > '2016-10-2' 
GROUP BY "userIdUser"
HAVING count(*) >= 5;

The double quotes go around an identifier. A qualified column reference such as diets.createdAt consists of two identifiers, so each needs to have the quotes (if you have them at all).

Otherwise, you are referring to a column whose name is "diets.createdAt". That is, the column name would have a period in it.

Upvotes: 2

Related Questions