visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
294 experts, 1172 registered users, 1625 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


The DDRK link is back online.
We got a one week service interruption, sorry.
(23-30/01/2010)

Databases :: MySql :: Value that occurs most Often
By: Squibi U.S.A.  Date: 04/06/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
Hi,

How do I obtain the value that occurs most often for a specific group?

For example If I have a table as follows:

id - int
location - varchar
smoker - int

So a smoker is indicated by a 1. And locations could be anywhere. How would I find out which location has the highest level of smokers?

This is using mysql 3.23 so subselects are not possible. And I can't upgrade.

Any help appreciated.
By: monange Date: 04/06/2003 21:20:00 English  Type : Comment
Does this produce the correct result:

Select location, count(*)
from YourTable
where smoker = 1
group by location
limit 1

CHeers
By: VGR Date: 04/06/2003 21:24:00 English  Type : Answer
not easy at all.

I recommend a simple front-end (PHP) script, like this algorithm :

<?php
// initialize results structure
$locations=array();
$i=0; // counter of locations found
// initialize DB access
$connection1=mysql_connect($dbHost,$dbuser,$dbPassword) or die('could not connect1');
$connection2=mysql_connect($dbHost,$dbuser,$dbPassword) or die('could not connect2');
mysql_select_db($dbname,$connection1) or die('could not select DB1');
mysql_select_db($dbname,$connection2) or die('could not select DB2');
$yourtable='test'; // change this!

$query="SELECT DISTINCT location FROM $yourtable;";
// exec query on connection1
$result=mysql_query($query,$connection1) or die('invalid query '$query' : ".mysql_error());
// process results
while ($res=mysql_fetch_array($result)) {
$i++; // counter
// memorize location
$locations[$i]['location']=$res['location'];
// for this location, let's count occurences
$query="SELECT SUM(smoker) AS a FROM $yourtable WHERE location='{$res['location']}';";
// exec query on connection2
$result2=mysql_query($query,$connection2) or die('invalid query '$query' : ".mysql_error());
$res2=mysql_fetch_array($result2);
$nbsmokers=$res2['a'];
// memorize smokers
$locations[$i]['smokers']=$nbsmokers;
} // while locations found
// on exit, you've $i valid locations with data in $locations[0..$i]['location' | 'smokers']
?>

Do register to be able to answer

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 63.321110 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page