Need help with group By

Question

Post reply

Need help with group By

Barcelona10

SSCertifiable

Points: 5065
More actions
September 11, 2015 at 11:38 am

#320526

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 2

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