I have two tables.
One containing countries with an ID and the geometry
The other containing customers with a name, geometry and a boolean value showing if they are active or not.
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
I want to find out, how many active customers there are in each country.
Step 1 - Join data: I could join the two tables based on the geometry. With ST_Contains I can test, which of the customers is located in which country. This works out well. I get as result a table from Customers which has additionally the column of the country ID. In addition, only customers with an active status are returned.
This is my SQL statement:
SELECT customers.*, countries.id
FROM customers
JOIN countries ON ST_Contains(countries.geometry, customers.geometry)
WHERE customers.statusActive IS TRUE
This is the resulting table:
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
Step 2- Sum up the customers in each country. I do not know how I can achieve this. I already tried to use GROUP BY based on the country ID, but this results in an error, that I have to include all columns from the SELECT statement. When I remove the customers.* (all fields from customers) from the SELECT, the query is running forever. This is how the desired result should look like:
NumberActiveUsers ID
1 1634
2 2357
0 3345
2 4694
Do I need another function to sum up the entries, or is GROUP BY the correct function?