Posgres 數據庫需要一個 group by 子句。（列 ‘candidates.id’ 必須出現在 GROUP BY 子句中或用於聚合函式中’，）

我有 2 張桌子，代理人和候選人。候選表有一個外鍵“agent_id”。我想獲取所有代理並彙總每個代理對象的候選計數；不同的候選人數有不同的條件要滿足。

我正在使用 nodejs、sequelize v4 和 postgresql

下面的 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 列“candidates.id”必須出現在 GROUP BY 子句中或在聚合函式中使用

錯誤詳情

"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":{}}

如果我將“candidates.id”添加到 group by 子句中，它不會引發錯誤，但也不會返回我想要的結果。請我想知道為什麼會發生此錯誤以及如何在不添加不必要的分組的情況下獲得我的預期結果。

在聚合查詢中，每個選定的列都必須包含在“group by”子句中或包裝在聚合函式中。您不能有任何“選定”列不在這兩種狀態中。

為什麼？

Postgres（或任何其他明智的 DBMS）如何知道給你哪個價值？

考慮這個例子：

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 |
+---+---+----+

“b”的什麼值應該代替上面的問號？應該是2還是4？

查詢中沒有任何內容可以確定應該返回哪一個，因此 Postgres 將簡單地選擇根本不執行查詢，而是拋出此錯誤。

我想你需要

$$ at least $$兩個查詢 - 一個獲取代理數據和候選人計數，另一個獲取候選人數據。

順便說一句，您需要使用“ SUM (c.active = false)”來“加起來”所有的真/假值。“COUNT(c.active = false)” 將返回與 “COUNT(c.active)” 或實際上是 COUNT(anything-field-is-not-null)" 相同的值。

引用自：https://dba.stackexchange.com/questions/275647