I'm trying to do something which might come down to logic, but I think mostly comes down to not knowing how to write it in an sql query. I've attached a sheet from Excel so that I can explain what I mean.
I need to figure out a way to count the number of different personal_id's per SSN e.g. for THES06001750Y there will be 2. In my whole data, some may only have one and some could have many, many more. I need this number so that I can divide into another value later on as it's to do with duplicates. I can't just do something clever with duplicates because I probably don't know how but mainly because I won't be able to access the other data, summarize both sets of data and track differences in the way I need.
I've had a couple of goes and the best (?!!!!) one so far seems to be to create a table based on distinct personal_id's, get the SSNs and then, in the main table, which has all the first set of data in it, add a column and try to join the tables with some kind of count. In the vain hope that today might be my lucky day, I tried the following:
UPDATE A SET PI_Counts = B.COUNT (personal_id)
FROM LFRD A LEFT OUTER JOIN LFRD_PI_Count B ON A.SSN = B.PI_SSNs;
I didn't really think it would work and, of course it didn't!
Please can somebody help me figure out a way to do this?