Finding the most common value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding the most common value

Author

Topic

khudson
Starting Member

3 Posts

Posted - 2010-01-20 : 09:54:37

Hello... hope someone can help a new user of SQL.
I am trying to find out which 'firstname' is most common from a list of over 20,000.

Is there any easy way of doing this?

I am a bit stuck since I'm a new user.

The table is called crw_personaldetails and the fields is called crw_forename.

Any suggestions would be much appreciated.

Thanks,
Kevin

brian147
Starting Member

10 Posts

Posted - 2010-01-20 : 10:13:47

try this:

select top 1 crw_forename
from crw_personaldetails
group by crw_forename
order by count(*) desc

khudson
Starting Member

3 Posts

Posted - 2010-01-20 : 10:25:19

Hi Brian
Thanks I tried that, and the code ran. However it doesn't seem to necessarily give the top '1' as the one which appears most frequently. I altered it slightly:

select top 1 crw_forename
from crwpersonaldetails
where crw_forename <> 'Unknown' and crw_forename <> '.'
group by crw_forename
order by count(*) desc

... and it gave 'Oleksandr' as most popular. I checked it elsewhere and it has 384 hits in the system. However the second name on the list, Sergiy, has 396?

Any ideas?

DDaku
Starting Member

6 Posts

Posted - 2010-01-20 : 10:35:04

I would remove the "top 1" while developing/testing, and then add it back in once you're happy that the first row is the correct one.

Try:
SELECT
lower(ltrim(rtrim(crw_forename))) as name, count(lower(ltrim(rtrim(crw_forename)))) as countOfName
FROM crwPersonalDetails
WHERE (lower(ltrim(rtrim(crw_forename))) <> 'unknown') AND (lower(ltrim(rtrim(crw_forename))) <> '.')
GROUP BY lower(ltrim(rtrim(crw_forename)))
ORDER BY count(lower(ltrim(rtrim(crw_forename)))) DESC

, which is basically exactly what you had in your 2nd post, but with the count displayed beside each name, and each occurance of the crw_forename field wrapped in a few functions to help clean up any errant data.

If that SELECT still gives Oleksandr first, and Sergiy second, I would assume that your other system (where you got the 384 and 396 numbers) has an error, or takes into account something else you're missing here.

khudson
Starting Member

3 Posts

Posted - 2010-01-20 : 10:37:36

THanks for the help. I got the same results, so I'll assume it's the other system with the problem!

Cheers.
Kevin

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 12:58:49

Is "Sergiy" spelt differently (capitalisation perhaps? or codepage / collation - e.g. an accent on the "e") such that SQL may regard them as different, but whatever confirmation-test you did above didn't take that into account?


SELECT crw_forename, COUNT(*)
from crwpersonaldetails
where RTrim(LTrim(crw_forename)) IN ('Oleksandr', 'Sergiy')
group by crw_forename

will show you what SQL Server thinks matches those two names

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources