1

I have the following query which gives me two columns store and customer_count...one row per store.

SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b 
WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id

This works perfectly.

What I need to add to this is another variable that represents the percentage of customers for each store. That is, customer_count/total number of rows in customer table. I'm just not sure how to structure this.

1
WITH sub_count AS (
  SELECT count(*) as total_cust from customers
  )

SELECT 
  a.id AS store,
  count(b.*), 
  count(b.*)::numeric/sub_count.total_cust AS customer_count 
FROM 
 stores a,
 customers b, 
 sub_count 
WHERE 
 ST_DWithin(a.the_geom::geography,b.the_geom::geography,5000) 
GROUP BY a.id, sub_count.total_cust

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.