SQL Query - Need some basic help

Question

I'm in need of some basic TSQL help. Here's my table layout:

Orders Table

• SSN
• ZipCode

ZipLookup Table

• ZipCode
• State

All columns are varchars.

How would I get a list of States with the number of distinct SSN's in each state? Preferably, if a particular SSN has orders from multiple states only the state with the most orders would be counted for that SSN.

Thank you for any tips you can give me.

Answer 1

First of all, you'd better make sure that you're allowed to be storing SSNs, because there are a whole bunch of privacy laws and regulations that may prohibit it.

The query would look like this:

SELECT z.State, COUNT(DISTINCT o.SSN) AS SsnCount
FROM ZipLookup z
INNER JOIN Orders o
    ON o.ZipCode = z.ZipCode
GROUP BY z.State

If you need to only count the SSN in its most-frequently-used State:

WITH StateSSNs AS
(
    SELECT
        o.SSN, z.State,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS RowNum
    FROM Orders o
    INNER JOIN ZipLookup z
        ON z.ZipCode = o.ZipCode
    GROUP BY o.SSN, z.State
)
SELECT z.State, COUNT(*) AS SsnCount
FROM ZipLookup z
INNER JOIN StateSSNs s
    ON s.State = z.State
WHERE s.RowNum = 1
GROUP BY z.State

Performance isn't going to be very good, I think this will require at least one full scan and maybe two, but if you want anything better then you'll need to normalize the schema.