聯接表並根據條件匯總行

我有兩張桌子。

• 一個包含具有 ID 和幾何的國家
• 另一個包含帶有名稱、幾何形狀和布爾值的客戶，顯示他們是否處於活動狀態。

Countries
iD     name     geometry
1634   UK       xxx
2357   USA      xxx
3345   Mexico   xxx
4694   Italy    xxx


Customers
name         geometry    statusActive
Hernandez    xxx         TRUE
Taylor       xxx         FALSE
Monte        xxx         TRUE
Winter       xxx         TRUE
Best         xxx         TRUE
Twist        xxx         TRUE

我想知道每個國家有多少活躍客戶。

第 1 步 - 連接數據：我可以根據幾何形狀連接兩個表。使用 ST_Contains 我可以測試哪些客戶位於哪個國家/地區。這效果很好。作為結果，我從客戶那裡得到了一個表格，其中還包含國家 ID 列。此外，僅返回具有活動狀態的客戶。

這是我的 SQL 語句：

SELECT customers.*, countries.id
FROM customers
JOIN countries ON ST_Contains(countries.geometry, customers.geometry)
WHERE customers.statusActive IS TRUE

這是結果表：

Customers
name        geometry    statusActive    id
Hernandez   xxx         TRUE            1634
Monte       xxx         TRUE            4694
Winter      xxx         TRUE            2357
Best        xxx         TRUE            2357
Twist       xxx         TRUE            4694

第 2 步 - 總結每個國家/地區的客戶。我不知道我怎麼能做到這一點。我已經嘗試根據國家 ID 使用 GROUP BY，但這會導致錯誤，我必須包含 SELECT 語句中的所有列。當我從 SELECT 中刪除客戶。*（客戶的所有欄位）時，查詢將永遠執行。這就是期望的結果應該是這樣的：

NumberActiveUsers   ID
1                   1634
2                   2357
0                   3345    
2                   4694

我需要另一個函式來總結條目，還是 GROUP BY 是正確的函式？

我必須編輯一些部分才能獲得查詢，因為我沒有幾何圖形

CREATE TABLE countries (
  "id" INTEGER,
  "name" VARCHAR(6),
  "geometry" VARCHAR(3)
);

INSERT INTO countries
  ("id", "name", "geometry")
VALUES
  ('1634', 'UK', 'xxx'),
  ('2357', 'USA', 'yyy'),
  ('3345', 'Mexico', 'zzz'),
  ('4694', 'Italy', 'aaa');

CREATE TABLE customers (
  "name" VARCHAR(9),
  "geometry" VARCHAR(3),
  "statusActive" boolean
);

INSERT INTO customers
  ("name", "geometry", "statusActive")
VALUES
  ('Hernandez', 'xxx', 'TRUE'),
  ('Taylor', 'xxx', 'FALSE'),
  ('Monte', 'aaa', 'TRUE'),
  ('Winter', 'yyy', 'TRUE'),
  ('Best', 'yyy', 'TRUE'),
  ('Twist', 'aaa', 'TRUE');

SELECT  COALESCE(countr,0) NumberActiveUsers ,co.id
FROM
(SELECT countries.id, Count(*) countr
FROM customers
JOIN countries ON countries.geometry =  customers.geometry
WHERE "customers"."statusActive" IS TRUE
GROUP BY countries.id) t1 RIGHT JOIN countries co ON t1.id = co.id

活躍使用者數 | ID
----------------: | ---:
 1 | 1634
 2 | 2357
 0 | 3345
 2 | 4694

db<>在這裡擺弄

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