0

I have a task where for each store location I have, I need to get the total number of customers within 5 miles as well as the total number of customers that have a specific status from those results. So say there are 850 customers within the area of store #1. I would also like to know out of all those customers how many have a status of 'Current', 'Inactive', or 'Active'. My result should look something like this:

| Store  | Total Customers | Current | Inactive | Active |
| Store 1| 850             | 300     | 50       | 200    |

My customers have a CustomerID, Longitude, Latitude, Status, as well as other columns.

My stores have a StoreID, Longitude, Latitude, and other columns that don't pertain to this example

This is my current query that gets the total number of customers within 5 miles of each one of our stores.

SELECT s.StoreID
    , sum(( 3959 * acos( cos( radians(s.Latitude) ) * cos( radians( c.Lat ) ) 
 * cos( radians( c.Lng ) - radians(s.Longitude) ) + sin( radians(s.Latitude) ) * sin(radians(c.Lat)) ) ) <= 5) AS customers 
  from ata_console.Stores s 
 cross 
  join ata_console.Customers c  
 group 
    by s.StoreID;

I can't find anything online that relates to a query such as this, because I feel like it's so unique to my situation. If anyone could help me figure out how to modify my current query to what I'm looking for then that would be great, TIA!

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Michael
  • 1,454
  • 3
  • 19
  • 45
  • So, you need conditional counting. See answers to the duplicate queston on achieving it. – Shadow Nov 13 '20 at 21:21
  • @Shadow the duplicate answer is only showing it for one condition. My example has multiple conditions. Where the customer has to be within 5 miles, and then having to get the totals of each status of those customers within 5. The duplicate question is just explaining if I just wanted to get the total number of customers with X status. – Michael Nov 13 '20 at 21:31
  • 1
    The distance goes into the where clause. You are overcomplicating this. – Shadow Nov 14 '20 at 02:04
  • 1
    @Shadow Moving the distance formula into the where clause, along with using the methods in the duplicate answer have helped me solve this issue. Thank you! – Michael Nov 16 '20 at 14:59
  • Good to hear! :) – Shadow Nov 16 '20 at 17:58

0 Answers0