Join tables and summarize rows based on condition
I had to edit some parts to get a query, as i didn#t have geommetry


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

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




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

INSERT INTO customers
  (&quot;name&quot;, &quot;geometry&quot;, &quot;statusActive&quot;)
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 &quot;customers&quot;.&quot;statusActive&quot; IS TRUE
GROUP BY countries.id) t1 RIGHT JOIN countries co ON t1.id = co.id


numberactiveusers |   id
----------------: | ---:
                1 | 1634
                2 | 2357
                0 | 3345
                2 | 4694


db&lt;&gt;fiddle here