Distinct Values

Question

I have a table with State, City, Town, and Zip code. The zip code has duplicate data since some zip codes have multiple towns. Now I need a query to return all 4 columns, however I do not want to repeat the zip code in the results. I only want to return 1 record per zip code. Where there are multiple towns I would like to return "-----------" in the result records to indicate multiple towns.

How can I do this?

Answer 1

SELECT CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(Town) END AS Town, Zip
FROM YourTable
GROUP BY Zip

Edit: if you also want to return other fields like the State, just add on e.g.:

SELECT 
    CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(Town) END AS Town, 
    CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(State) END AS State, 
    Zip
FROM YourTable
GROUP BY Zip

Answer 2

I'm assuming the zip codes can only be related to one city and state.

SELECT State
, City
, CASE WHEN MAX(Town) <> MIN(Town) THEN '-------' ELSE MAX(Town) END as Town
, ZipCode
FROM data
GROUP BY State, City, ZipCode

soomething like that? Not sure how efficient it is vs other options though.

EDIT: I agree that COUNT is a clearer way to express this