Posgres database requires a group by clause. (column \"candidates.id\" must appear in the GROUP BY clause or be used in an aggregate function",)

Question

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.

Answer 1

In an aggregate query, every select'ed column must either be included in the "group by" clause or be wrapped in an aggregating function. You cannot have any 'select'ed column not in either of these states.

Why?

How would Postgres (or any other, sensible, DBMS for that matter) know which value to give you?

Consider this example:

select a, b, c, 
from table1 
order by a, b, c ; 

+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 1 | 4 | 6 | 
+---+---+---+

select a, b, count( c ) cc 
from table1 
group by a 
order by a ; 

+---+---+----+
| a | b | cc | 
+---+---+----+
| 1 | ? |  2 |
+---+---+----+

What value of "b" should replace the question mark, above? Should it be 2 or 4?

There is nothing in the query to determine which one should be returned so Postgres will simply opt not to run the query at all, throwing this error instead.

I think you need [at least] two queries - one to get the Agent data and counts of Candidates and the other to get Candidate data.
BTW, you need to use "SUM(c.active = false)" to "add up" all the true/false values. "COUNT(c.active = false)" will return the same value as "COUNT(c.active)" or, indeed, COUNT(anything-field-is-not-null)".