0

I have a list of customers with an x and y co-ordinate for each between -50 and 50 for each. I need to calculate the number of customers in each 10x10 grid square but don't know how I can group customers by a range in two columns. The Customer table has a customerID, customer_x and customer_y column. I've managed to get the result for a single column using

SELECT 10 * ( customer_x / 10 ) AS start_range, 10 * ( customer_x / 10 ) + 9 AS end_range, count(*) AS COUNT FROM t_customer GROUP BY customer_x / 10

I found this here, but can't work out how to get this working with a 2 dimensional range. I've started writing out a case for each grid-square using Select * from customer where (customer_x, customer_y) between (-50,-50) and (-50,-40) and so on, but there's 100 in total and I'm assuming there's a more efficient way? I'm using SQLite3. Any help would be really appreciated.

I forgot to add, it actually only needs to show the grid square with the highest number of customers, so there may be a far easier way of doing this?

Community
  • 1
  • 1
Dave C
  • 367
  • 5
  • 19

1 Answers1

0
GROUP BY customer_x / 10, customer_y / 10
Dmitry Kurbsky
  • 131
  • 4
  • I just tried `SELECT 10 * ( customer_x / 10 ) AS start_range, 10 * ( customer_x / 10 ) + 9 AS end_range, count(*) AS COUNT FROM t_customer GROUP BY customer_x / 10, customer_y / 10`but it's still not giving the required answer. – Dave C Jan 10 '17 at 01:09
  • 1
    SELECT customer_x / 10, customer_y / 10, COUNT(*) FROM t_customer GROUP BY customer_x / 10, customer_y / 10 – Dmitry Kurbsky Jan 10 '17 at 01:13
  • Thanks @dmitry-kurbsky, that seems to work, but I changed it to `SELECT 10*(customer_x / 10), 10*(customer_y / 10), COUNT(*) FROM t_customer GROUP BY customer_x / 10, customer_y / 10;` so that the co-ordinate ranges were maintained. How would I go about ordering by the new frequency column? – Dave C Jan 10 '17 at 01:22
  • 1
    add ORDER BY 3 clause, where 3 is a number of the column – Dmitry Kurbsky Jan 10 '17 at 01:29
  • 1
    Or be a little more mnemonic and give that column a name, i.e. in the select say `COUNT(*) as freq` and then use the name `freq` in the `ORDER BY`. – MAP Jan 10 '17 at 02:57
  • Is there a way to include null values, i.e. grid squares that are unpopulated to the results? Alternatively just listing grid squares with no customers in is also feasible. – Dave C Jan 10 '17 at 14:03
  • You cannot select something that not exists. The only way is to have a full grid and then join it with the actual data. Like this: WITH RECURSIVE zz AS ( SELECT -5 AS z UNION ALL SELECT z+1 FROM zz WHERE z<=4) SELECT 10*(cc.customer_x/10),10*(cc.customer_y/10),COUNT(*) FROM zz AS xx CROSS JOIN zz AS yy LEFT JOIN t_customer AS cc ON cc.customer_x/10=xx.z AND cc.customer_y/10=yy.z GROUP BY cc.customer_x/10,cc.customer_y/10 ORDER BY 3 – Dmitry Kurbsky Jan 10 '17 at 14:48
  • Is there a simpler way just to count the number of rows in the results, and minus it from 100 (the number of grids in a 10 x 10 grid)? I think it should work, just not sure how to count the results from the above query? – Dave C Jan 11 '17 at 20:53
  • @Dave C, what result are you going to get? – Dmitry Kurbsky Jan 12 '17 at 15:05
  • @DmitryKurbsky, I've decided to create another table of all 100 possible grids. I currently have this adapted formula as I realised that the negative grids weren't correct: `SELECT 10 * (t.customer_x / 10), 10 * (t.customer_y / 10), COUNT(*) FROM (SELECT CASE WHEN customer_x < 0 THEN customer_x - 10 ELSE customer_x END AS customer_x, CASE WHEN customer_y < 0 THEN customer_y - 10 ELSE customer_y END AS customer_y FROM t_customer) t GROUP BY t.customer_x / 10, t.customer_y / 10 ORDER BY 3 DESC;`, but now I just need to understand how to subtract this from my other table `all_grid`. – Dave C Jan 12 '17 at 15:09