0

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.

2
  • Remove all the candidates.XXX from the SELECT list, except the COUNTs. Sep 17, 2020 at 12:21
  • Worked like a charm! Thank you so much :) Sep 17, 2020 at 12:30

1 Answer 1

3

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)".

1
  • Thanks for the detailed explanation :) The SUM part especially really helped, I was wondering why all the numbers where the same lol Sep 17, 2020 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.