how to count unique id entry?

Question

Post reply

how to count unique id entry?

asco5

Say Hey Kid

Points: 665
More actions
March 20, 2013 at 4:51 am

#394432

Hi i have a table in sql i would like to count how many times i have an unique ID
as you can see in my table example each person have different personid that mean everytime i enroll him he have a new personid
but each person get a unique id number
i would like to know how many jack i have in my table
how many mike
and how many marie
depending on the id cause its the only unique entry in my table
it should tell me in a new column for example
mike 2
jack 2
marie 1

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

You want to run something along the lines of:-

select

[Name],

count(ID)

from

tablename

group by [Name]

Am I mis-reading the data or have you got duplicates in there? Maybe ID does not relate to PersonId

To an outsider it could be confusing

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Am I mis-reading the data or have you got duplicates in there? Maybe ID does not relate to PersonId
To an outsider it could be confusing

Stuart is right. To an outsider, it does look confusing. From the data, it looks like the name corresponds to the ID and the PersonID isn't really a "person's ID" but more an identifier for the row. I would have expected the exact opposite - where the PersonID is related to the person and ID is the row identifier.

Either way, the GROUP BY is the statement to get the results you want.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

You'll also get coded responses if you take the small bit of time to provide your data in a readily consumable form instead of a graphic. Read the article at the first link in my signature line below help us help you more quickly in the future. The method takes less time than it does for you to post a graphic so you'll see a time savings, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)