Search code examples
sqlcounthaving

SQL Unique 5 character string

I've got a table that has a Varchar column "Zip" that contains some 5 digit zip codes ex 12345 and some Zip+4 zip codes ex 12345-9999. They are all unique.

How do I find the Duplicates (Or unique) values for only the first 5 characters?

When I run this I get 29,722 records which is the total records in the table and I can clearly see that both Zip 00690 and 00690-7998 are returned.

SELECT ZIP
FROM tblSTKUniqueZip
GROUP BY ZIP
HAVING COUNT(LEFT(ZIP,5)) = 1

When I run this I get zero records returned 

SELECT ZIP
FROM tblSTKUniqueZip
GROUP BY ZIP
HAVING COUNT(LEFT(ZIP,5)) > 1

What am I not grasping on the HAVING clause. I'd expect it to get the left 5 characters and either give me the unique ones or the duplicates.

Solution

  • You need to both select and group by your left 5 characters of the zip. Once you have that grouping done, you can use count(*) to determine the actual count of rows within the group. Try this:

    SELECT LEFT(ZIP,5), count(*)
FROM tblSTKUniqueZip
GROUP BY LEFT(ZIP,5)
HAVING COUNT(*) > 1