Counting in sql

Hi All

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?

Thanks
Sarah
EE_Count_Example.xlsx
ScuzzyJoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
And I believe your update will look like:

UPDATE A SET A.PI_Counts = COUNT(c.personal_id)
FROM LFRD A
INNER JOIN (
	SELECT personal_id, ssn
    FROM      #tmp_test
    GROUP BY  personal_id, ssn
) c ON c.ssn = A.ssn
GROUP BY c.ssn

Open in new window

0
 
zephyr_hex (Megan)DeveloperCommented:
Here's one way to do it:

SELECT  COUNT(personal_id) AS cnt ,
        ssn
FROM    ( SELECT    personal_id ,
                    ssn
          FROM      #tmp_test
          GROUP BY  personal_id ,
                    ssn
        ) c
GROUP BY ssn;

Open in new window


I first get the distinct records from the table (using grouping), then do the count
0
 
ScuzzyJoAuthor Commented:
Hi Zeyphyr_hex

Thanks, am trying it now, but how do I get the results into a table?  I know I'm going to put "into" somewhere but I'm not sure where.

Thanks
Sarah
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Below will insert the result set in to a temp table


SELECT  COUNT(personal_id) AS cnt ,
        ssn
INTO #Temp
FROM    ( SELECT    personal_id ,
                    ssn
          FROM      #tmp_test
          GROUP BY  personal_id ,
                    ssn
        ) c
GROUP BY ssn;

Open in new window

0
 
ScuzzyJoAuthor Commented:
Hi

Stupid me!  I knew that - where to put the into bit.

Your solution works perfectly.  Thanks for your help.

Thanks
Sarah
0
 
ScuzzyJoAuthor Commented:
Really quick and good answers.  Thanks to both of you.
0
 
Pawan KumarDatabase ExpertCommented:
Welcome :)
0
All Courses

From novice to tech pro — start learning today.