September 11, 2015 at 11:38 am
Hi ,
i have a table like this:
BLOCKSTREETCITYGEO_XGEO_Y
25XYZ DRLos Angeles123456789
25XYZ DRLos Angeles123456789
25XYZ DRLos Angeles123006700
25XYZ DRLos Angeles123456789
30ABC AVE Pasadena 11223 55667
30ABC AVE Pasadena 1122355667
30ABC AVE Pasadena 1122355667
30ABC AVE Pasadena 1129955699
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