1

I asked this question yesterday on how to calculate the number of customers in a grid square, and the solution I got was:

SELECT 10 * (customer_x / 10), 10 * (customer_y / 10), COUNT (*) FROM t_customer GROUP BY customer_x / 10, customer_y / 10 ORDER BY 3 DESC;

Now I need to present the grid squares that contain zero customers, and I'm not sure how to do this as the query is based on calculations of the usual numbers and the grid doesn't actually exist in the table. Should I use an ISNULL() function? The results for this query in my current database are:

90|90|7
30|20|4
-20|-40|2
-10|-20|2
-10|-10|2
-40|-40|1
-40|-30|1
-40|30|1
-30|0|1
-20|0|1
-20|30|1
-10|-30|1
-10|40|1
0|-20|1
0|-10|1
0|0|1
0|10|1
0|40|1
10|20|1
20|20|1
30|-40|1
30|30|1

But given that there are 100 grid squares in the area there are many without customers. I just need a query that will show all but the above grid squares. I'm using SQLite3 and any help would be greatly appreciated. Those in the (90,90) grid square are to be ignored.

Sample data:

Sample Data Image

I need to have a list of grid squares with no customers in from the 100 grid squares in a 10 x 10 grid (-50 to +50 in increments of 10). It may be easier to use a virtual table of all possible grid squares and to subtract the above query from it?

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

1 Answers1

0

To make an (empty) grid square show up, add a dummy row for it, with a customer_id of NULL. NULL values are not counted:

SELECT ...x...,
       ...y...,
       COUNT(customer_id)
FROM t_customer
GROUP BY ...x..., ...y...;

If you cannot change the database, you have to generate the empty rows with a recursive common table expression:

WITH RECURSIVE range(i) AS (
  -- from -90 to +90 in steps of 10
  SELECT -90
  UNION ALL
  SELECT i + 10
  FROM range
  LIMIT 19
), empty(x, y, customer_id) AS (
  SELECT x.i,
         y.i,
         NULL
  FROM       range AS x
  CROSS JOIN range AS y
)
SELECT ...x...,
       ...y...,
       COUNT(customer_id)
FROM (SELECT * FROM t_customer
      UNION ALL
      SELECT * FROM empty)
GROUP BY ...x..., ...y...;
CL.
  • 173,858
  • 17
  • 217
  • 259
  • I'm not sure I quite get what you mean? I can't change the actual database, it all has to be done within a query. – Dave C Jan 10 '17 at 21:51
  • Should I have entered this verbatim? Inputting this into the terminal, I get `Error: near ".": syntax error` – Dave C Jan 11 '17 at 09:14
  • That `...x...` represents your coordinate rounding stuff. – CL. Jan 11 '17 at 09:17
  • This is producing the following result: -90||19 `-80||19 -70||19 -60||19 -50||19 -40||19 -40|-440|1 -40|-390|1 -40|350|1 -30||19 -30|-40|1 -20||19 -20|-420|2 -20|-30|1 -20|300|1 -10||19 -10|-310|1 -10|-240|2 -10|-100|2 -10|410|1 0||19 0|-290|1 0|-190|1 0|60|1 0|140|1 0|400|1 10||19 10|280|1 20||19 20|250|1 30||19 30|-460|1 30|240|4 30|360|1 40||19 50||19 60||19 70||19 80||19 90||19 90|990|7` I'm not sure this is right? – Dave C Jan 11 '17 at 09:22
  • I'm not sure either. How should I know what coordinates you want, or what query you are actually using? – CL. Jan 11 '17 at 09:28