how to find number of occurences of a value in a field?

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

I have a recordset with say 3 fields: FirstName, LastName, Address. I want
to run a query to determine which LastName appears the most amount of times
and how many times that was. AND another query to find the opposite: which
one appears the least amount of times and how many times that was.

can I do this with sql?

note: there would be far to many unique 'LastName' s to write a seperate
select query on each one and compare the count.

anyone?
 
Here is sample SQL that will show the LastName with most occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName) DESC;


Here is sample for the fewest occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName);
 
Sweet! you rule!

Ken Snell said:
Here is sample SQL that will show the LastName with most occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName) DESC;


Here is sample for the fewest occurrences:

SELECT TOP 1 Count(LastName) AS CountOfLastName, LastName
FROM TableName
GROUP BY LastName
ORDER BY Count(LastName);
Click to expand...
 
Back
Top