I have a conundrum i'm trying to figure out and hoping for some help. In my simplified example below, I am looking for a query that will distinctly count the number of IDs in column 1 based on the criteria that City = Chicago and Existing Customer = Yes. The problem is that there are some IDs that meet both criteria, but not on the same record.
I can write a query that gives me a count of 1 (the ID that has both criteria on the same record), but I need a query that will give me a count of 2. Is this possible in SQL? Any guidance is appreciated. Thanks!
ID
City
Existing Customer
123456
Chicago
123456
Yes
789012
Chicago
Yes