Easy way to Calculate Mode

[mp141]

Hello,

I was wondering if there was an easy way for me to calculate the mode
for a field of values in one query. I have one field of values say
Names and all I want it to return is one record of the most common
name in my Names field. I tried a count function subquery within a max
function but it returns an error that I cannot aggregate functions...
*shrugs* I am quite a newbie at this so please forgive my ignorance.
Any help is much appreciated! Thanks in advance!

-mp

[Phil Wyatt]

I think you are after something like this if I understand you correctly

SELECT TOP 1 YourTable.YourNameField, Count(YourTable.YourNameField) AS
CountOfYourNameField

FROM YourTable

GROUP BY YourTable.YourNameField;

"mp141" <mp**@141xm.com> wrote in message
news:e9**************************@posting.google.c om...

Hello,

I was wondering if there was an easy way for me to calculate the mode
for a field of values in one query. I have one field of values say
Names and all I want it to return is one record of the most common
name in my Names field. I tried a count function subquery within a max
function but it returns an error that I cannot aggregate functions...
*shrugs* I am quite a newbie at this so please forgive my ignorance.
Any help is much appreciated! Thanks in advance!

-mp

[John Winterbottom]

"mp141" <mp**@141xm.com> wrote in message
news:e9**************************@posting.google.c om...

Hello,

I was wondering if there was an easy way for me to calculate the mode
for a field of values in one query. I have one field of values say
Names and all I want it to return is one record of the most common
name in my Names field. I tried a count function subquery within a max
function but it returns an error that I cannot aggregate functions...
*shrugs* I am quite a newbie at this so please forgive my ignorance.
Any help is much appreciated! Thanks in advance!

-mp

for a table called 'names', having a column called 'name', (consider
changing that - it's a reserved word), you can do this
select top 1 [name], count(*) as freq
from names
group by [name]
order by count(*) desc

[Eric Schittlipz]

"mp141" <mp**@141xm.com> wrote in message
news:e9**************************@posting.google.c om...

Hello,

I was wondering if there was an easy way for me to calculate the mode
for a field of values in one query. I have one field of values say
Names and all I want it to return is one record of the most common
name in my Names field. I tried a count function subquery within a max
function but it returns an error that I cannot aggregate functions...
*shrugs* I am quite a newbie at this so please forgive my ignorance.
Any help is much appreciated! Thanks in advance!

-mp

What happens if two names, say Alan and Bob both appear 5 times? Which
would you select? The query below would select only Alan's record because
his name comes first alphabetically:

SELECT TOP 1
ConName, Count(*) AS Mode
FROM tblContacts
GROUP BY ConName
ORDER BY Count(*) DESC , ConName ASC

[M Pan]

Thank you Phil but I don't think that solved my problem. It is giving me
some arbitrary value (at least it looks arbitrary to me). Maybe I should
elaborate: I have a field that looks like this

Answer
1
2
3
4
2
2
Would I would like the query to do is return the mode, in this case "2".
Sounds easy but it's got me completely frazzled!

Thanks again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phil Wyatt]

Does this fit the bill?

SELECT TOP 1 Questionaire.Answer
FROM Questionaire
GROUP BY Questionaire.QuestionNumber, Questionaire.Answer
ORDER BY Count(Questionaire.Answer) DESC;

"M Pan" <mp**@141xm.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thank you Phil but I don't think that solved my problem. It is giving me
some arbitrary value (at least it looks arbitrary to me). Maybe I should
elaborate: I have a field that looks like this

Answer
1
2
3
4
2
2
Would I would like the query to do is return the mode, in this case "2".
Sounds easy but it's got me completely frazzled!

Thanks again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!