September 11, 2015 at 11:38 am
Hi ,
i have a table like this:
BLOCK STREET CITY GEO_X GEO_Y
25 XYZ DR Los Angeles 12345 6789
25 XYZ DR Los Angeles 12345 6789
25 XYZ DR Los Angeles 12300 6700
25 XYZ DR Los Angeles 12345 6789
30 ABC AVE Pasadena 11223 55667
30 ABC AVE Pasadena 11223 55667
30 ABC AVE Pasadena 11223 55667
30 ABC AVE Pasadena 11299 55699
As you can see there are different GEO_X and GEO_Y for same address.i want to find total number for same address and how many out of them have different GEO_X/GEO_Y
Thank you advance
September 11, 2015 at 11:49 am
You could use something like this:
WITH Addresses AS(
SELECT *,
DENSE_RANK() OVER (PARTITION BY BLOCK, STREET, CITY ORDER BY GEO_X, GEO_Y) r
FROM YourTable
)
SELECT BLOCK, STREET, CITY, MAX(r) DistinctGEOs
FROM Addresses
GROUP BY BLOCK, STREET, CITY
HAVING MAX(r) > 1
September 11, 2015 at 12:05 pm
It's helpful to know what you mean here, and you should clarify if you need to see the geo codes in the result.
If not, this is really a simple query. Luis probably has the best way to do this. You certainly could do a count in the CTE and then count the "counts" in the outer query, but this works fine.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 11, 2015 at 12:48 pm
Sorry if i was not clear.This is wat i want to see in my results;
BLOCK STREET CITY NoMatchTotal TOTAL
Luis's script is what i want except TOTAL is missing.Like out of 10 this address has 2 mismatch GEO's
Thank you
September 11, 2015 at 12:57 pm
i got it.Just added COUNT(*) to Luis's script.Thank you guys
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply