Jump to content


Photo

Returning the modal value from a selected column?


  • Please log in to reply
3 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 29 November 2005 - 10:00 AM

Is there a function in MySQL that you can use in a query to find the modal (most common) value. So if I had a column called age, and it held the values 12, 13, 13, 13, 12, 12, 11, 14, 14, 13 - The mode would be '13'.

I'm thinking there must be an easy way to achieve this with a MySQL query rather than running all the results through a bunch of loops in PHP...

As always, any help is very much appreciated.

EDIT: I have just wrote a small function in PHP to work out the mode of an array of numbers, but I would still be interested if someone knows a way to do this with MySQL.

#2 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 29 November 2005 - 10:48 AM

[!--quoteo(post=322999:date=Nov 29 2005, 06:00 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Nov 29 2005, 06:00 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Is there a function in MySQL that you can use in a query to find the modal (most common) value. So if I had a column called age, and it held the values 12, 13, 13, 13, 12, 12, 11, 14, 14, 13 - The mode would be '13'.

I'm thinking there must be an easy way to achieve this with a MySQL query rather than running all the results through a bunch of loops in PHP...

As always, any help is very much appreciated.

EDIT: I have just wrote a small function in PHP to work out the mode of an array of numbers, but I would still be interested if someone knows a way to do this with MySQL.
[/quote]

Can not test this just now, but would this be a step in the right direction:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] MAX(COUNT(`field`)) FROM `table` GROUP BY `field` [!--sql2--][/div][!--sql3--]
Cook

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 29 November 2005 - 10:51 AM

[!--quoteo(post=323001:date=Nov 29 2005, 10:48 AM:name=Cook)--][div class=\'quotetop\']QUOTE(Cook @ Nov 29 2005, 10:48 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Can not test this just now, but would this be a step in the right direction:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] MAX(COUNT(`field`)) FROM `table` GROUP BY `field` [!--sql2--][/div][!--sql3--]
[/quote]
Thanks again Cook, that appears to make sense! I'll give it a go in a bit.

#4 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 29 November 2005 - 10:58 AM

Or this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] `field`, COUNT(`field`) AS `count` FROM `table` GROUP BY `field` ORDER BY `count` DESC LIMIT 1 [!--sql2--][/div][!--sql3--]
Cook




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users