Count Unique

  • Thread starter Thread starter Clueless
  • Start date Start date
C

Clueless

I have a table with various fields. I want to count one
of the fields (text). This field contains people's
names, but can contain the person's name more than once.

I want to count the unique names.

I cannot figure out how to do this.
 
Dear Clue:

SELECT COUNT(*) FROM (SELECT DISTINCT YourColumn FROM YourTable) T

This assumes that when a name is in the column twice, it really is the
same person, not two people with the same name. Well, you didn't
expect the comuter to figure that out for you, did you? But, you did
say, "unique names," didn't you!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT COUNT(PersonName) As PersonCount
FROM TableName
GROUP BY PersonName

This assumes the duplicate PersonName value are spelled the same. E.g.:

Fred Johnson <> F. Johnson <> Fred L. Johnson

Fred Johnson = Fred Johnson

IOW, a human may "know" that Fred Johnson and Fred L. Johnson are the
same person, but the computer is only comparing characters and evaluates
those 2 strings as different, 'cuz one contains "L." and the other one
doesn't.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJW7ZoechKqOuFEgEQKf7gCg18w4O01gpzMmFf5l11aNZ0eZlGoAoNF5
PDm2OddEOrb2s3D6F/CatpYc
=ggy5
-----END PGP SIGNATURE-----
 
Yes, these names are spelt the same. The statements
provided have worked.

Thank you.
 
Back
Top