CODEWITHSUNDEEP
sqlms-access
Juicy
Juicy

Reputation: 12520

Return most frequent occurence(s)

I'm using MS Access 2010.

If I have a table like this:

Table1:

FirstName         LastName

  Alex              AAA
  Max               AAA
  Stewie            BBB
  Barney            BBB
  John              AAA

What query could return the most frequent LastName and how many times it occurs? In this case it should return:

AAA      3

But if a LastName is tied in frequency (occur the same amount of time) it should return both.

I searched the net and stackoverflow and although many questions are similar they either don't solve my problem or are for another dialect than SQL Server.

Thank you.

Upvotes: 3

Views: 7049

Answers (2)

Fionnuala
Fionnuala

Reputation: 91356

Top returns matches in MS Access:

SELECT TOP 1 * 
FROM (SELECT names.LastName, Count(names.LastName) AS CountOfLastName
      FROM [names]
      GROUP BY names.LastName) a
ORDER BY CountOfLastName Desc

So the above query will return more than one row when there is more than one row with the same frequency.

Upvotes: 2

radarbob
radarbob

Reputation: 5101

Select
   LastName,
   count (*) as HowManyTimes
from myTable
Group By LastName
Order By count(*)

Upvotes: 1

Related Questions