Databases :: MySql :: Value that occurs most Often |
|||
| By: Squibi |
Date: 04/06/2003 00:00:00 |
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 | 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 | 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: | |||
| |
|
|
|
| |
|
|
|








