I have 2 tables, agents and candidates. The candidates table has a foreign key "agent_id". I want to get all agents and aggregate the candidate counts on each agent object; the different candidate counts have different conditions to meet.
I am using nodejs, sequelize v4 and postgresql
The SQL query below throws an error that I do not understand.
QUERY
SELECT "Agent"."id", "Agent"."first_name", "Agent"."middle_name", "Agent"."last_name", "Agent"."phone", "Agent"."active", "Agent"."team_id", COUNT("candidates"."id") AS "total_count", COUNT("candidates"."active" = 'false') AS "pending_count", COUNT("candidates"."active" = 'true') AS "verified_count", "candidates"."id" AS "candidates.id", "candidates"."active" AS "candidates.active", "candidates"."client_approved" AS "candidates.client_approved", "candidates"."rejected" AS "candidates.rejected", "candidates"."date_disbursed" AS "candidates.date_disbursed", "candidates"."date_cashedout" AS "candidates.date_cashedout", "candidates"."agent_id" AS "candidates.agent_id" FROM "agents" AS "Agent" LEFT OUTER JOIN "candidates" AS "candidates" ON "Agent"."id" = "candidates"."agent_id" AND ("candidates"."deleted_at" > '2020-09-17 11:53:28.012 +00:00' OR "candidates"."deleted_at" IS NULL) WHERE ("Agent"."deleted_at" > '2020-09-17 11:53:28.012 +00:00' OR "Agent"."deleted_at" IS NULL) GROUP BY "Agent"."id" LIMIT '50';
ERROR column "candidates.id" must appear in the GROUP BY clause or be used in an aggregate function
ERROR DETAIL
"name":"SequelizeDatabaseError","parent":{"name":"error","length":177,"severity":"ERROR","code":"42803","position":"303","file":"parse_agg.c","line":"1388","routine":"check_ungrouped_columns_walker","sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';"},"original":{"name":"error","length":177,"severity":"ERROR","code":"42803","position":"303","file":"parse_agg.c","line":"1388","routine":"check_ungrouped_columns_walker","sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';"},"sql":"SELECT \"Agent\".\"id\", \"Agent\".\"first_name\", \"Agent\".\"middle_name\", \"Agent\".\"last_name\", \"Agent\".\"phone\", \"Agent\".\"active\", \"Agent\".\"team_id\", COUNT(\"candidates\".\"id\") AS \"total_count\", COUNT(\"candidates\".\"active\" = 'false') AS \"pending_count\", COUNT(\"candidates\".\"active\" = 'true') AS \"verified_count\", \"candidates\".\"id\" AS \"candidates.id\", \"candidates\".\"active\" AS \"candidates.active\", \"candidates\".\"client_approved\" AS \"candidates.client_approved\", \"candidates\".\"rejected\" AS \"candidates.rejected\", \"candidates\".\"date_disbursed\" AS \"candidates.date_disbursed\", \"candidates\".\"date_cashedout\" AS \"candidates.date_cashedout\", \"candidates\".\"agent_id\" AS \"candidates.agent_id\" FROM \"agents\" AS \"Agent\" LEFT OUTER JOIN \"candidates\" AS \"candidates\" ON \"Agent\".\"id\" = \"candidates\".\"agent_id\" AND (\"candidates\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"candidates\".\"deleted_at\" IS NULL) WHERE (\"Agent\".\"deleted_at\" > '2020-09-17 11:53:28.012 +00:00' OR \"Agent\".\"deleted_at\" IS NULL) GROUP BY \"Agent\".\"id\" LIMIT '50';","error@context":{}}
If I add the "candidates.id" to the group by clause it doesn't throw an error but it also doesn’t return the result I want. Please I would like to know why this error is happening and how to get my intended result without add the unnecessary grouping.
candidates.XXX
from theSELECT
list, except the COUNTs.