Calculating the mode

Hi there..

I am using access to work on some data.

I have a column of data and I need to find the mode for that set of data, except I have no idea how to do it in SQL and access.

what it the mode u ask?
If I have a set of data 2 4 3 5 5 5 7 3 4
the mode is the most commonly occuring data, which would be 5 in the above example.

any help would be appreciated.

Thank you

ok I was working on it and this is what I came up with

I broke it up into two parts

first part returns a table with number of occurances of a data

SELECT [age], count([age]) AS totalOccurances
FROM Person
GROUP BY [age];

the second part which I wrote doesn't seem to work in access altough it seems to make sense logically


SELECT [Person Query].age
FROM [Person Query]
HAVING MAX( [Person Query].totalOccurances) ;


also, if someone could somehow figure out how to combine those two queries... that would be great

you were pretty close

keeping your first query, and assuming you wanted the actual person with the mode, your second one would be

select name, age
from Person
where age =
( select top 1 age
from [Person Query]
order by totalOccurances desc )

for more good info on statistics and SQL, see
http://www.oreilly.com/catalog/tran...apter/ch08.html

rudy
http://r937.com/

thanks..

the link is very useful too..

any idea why what I implemented doesn't work ??

yeah, your query doesn't work because it's invalid syntax

ok.. the solution is not as easy as I thought it would be

say I have table like this'

LastName Age Frequency
Jack 12 1
Jack 22 3
Jack 2 1
Donald 3 2
Donald 34 1
Cray 3 3
Cray 5 1
Cray 27 2

For each last name I would like to find the most common age.
So I would like the following table returned

LastName Age
Jack 22
Donald 3
Cray 3


The current solution just returns the LastName with the most frequencies

you can use a correlated subquery --

select LastName, Age, Frequency
from tablelikethis X
where Frequency =
( select max(Frequency)
from tablelikethis
where LastName = X.LastName )

hi, thanks for ur help.. it gave me the general idea altough it didn't work

I think ur code would work on SQL Server, but doesn't work on Access

SELECT Freq.Name, Freq.Age
FROM Freq
WHERE Freq.Frequency = (SELECT MAX(Freq_1.Frequency)
From Freq AS Freq_1
WHERE Freq.Name = Freq_1.Name);