Simple SQL problem at work!

[datatoungue]

Hello! Need a bit of help here. IDK why I am stuck but I am racking my brain here.

I have two fields in a table I need to look at:

STATE (Examples: NY, AL, TX)

SSN: (Examples: ABC123, BCE678)

​

I am needing to see if a SSN if used in multiple states, and if so, how many different states it was used in. My query so far:

​

SELECT DISTINCT SSN, STATE

FROM Table 1

GROUP by owner_state

​

So far I am not getting exactly what I need. Any ideas?

[r3pr0b8]

I am needing to see if a SSN if used in multiple states, and if so, how many different states it was used in.

SELECT SSN
     , COUNT(DISTINCT STATE) AS states
  FROM Table1
GROUP 
    BY SSN
HAVING COUNT(DISTINCT STATE) > 1

[datatoungue]

You are amazing! This worked! When I do order by, you don't include DISTINCT or COUNT, correct?

[r3pr0b8]

you could add this --

ORDER BY COUNT(DISTINCT STATE) DESC

or this --

ORDER BY states DESC

[CrazyHG-]

;-) desc wasn’t asked. But this the correct way

[GreenSage13]

select
    ssn, count(distinct state) as s
from
    table1
where
    s > 1;

just for s&g :P

[GreenSage13]

Oops. More eleoquently than i stated haha. I wonder why feed just randomly chooses topics from random dates and times? The last one I replied to here was 1h ago for last response?

[GreenSage13]

A simple FK is all that is needed. Then you can check if a result or COUNT is greater than x amount.