I have a customer table with their closest delivery hub on a grid based system and need to calculate what is the most populated area using a query.
This is the current query I have that lists all of the Co-ordinates per Customer.
SELECT Customers.HubID, TO_CHAR(Hubs.HubCoordX, 'FM999999999999') as "X Co-ordinate", TO_CHAR(Hubs.HubCoordX, 'FM999999999999') AS "Y Co-ordinate" FROM Customers INNER JOIN Hubs ON Customers.HubID = Hubs.DestinationID ORDER BY Hubs.HubCoordX, Hubs.HubCoordY
This query creates the following result.
| HubID | X Co-ord | Y Co-ord |
|---|---|---|
| 9 | -3 | 1 |
| 11 | -2 | 18 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 1 | 0 | 0 |
| 1 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 3 | 1 |
| 5 | 3 | 1 |
| 7 | 7 | 3 |
But I need a result like this
| X Co-ordinate | Y Co-ordinate | Population |
|---|---|---|
| -3 | 1 | 1 |
| -2 | 18 | 1 |
| 0 | 0 | 6 |
| 3 | 1 | 2 |
| 7 | 3 | 1 |
Thanks in advance
I have attempted use Count Unique however it resulted in only counting individual Co-ordinates once.
CodePudding user response：
SELECT TO_CHAR(Hubs.HubCoordX, 'FM999999999999') as "X Co-ordinate", TO_CHAR(Hubs.HubCoordX, 'FM999999999999') AS "Y Co-ordinate", Count(HubID) as "Population" FROM Customers INNER JOIN Hubs ON Customers.HubID = Hubs.DestinationID Group BY Hubs.HubCoordX, Hubs.HubCoordY