I have a MySQL database with a table that has a field/column labled "user8" (which is the field for users' state).

I have this PHP call and its working fine if I call all states individually and I only want numbers of rows.

<?PHP 
mysql_select_db("freeskat_promail") or die(mysql_error());
$result = mysql_query("SELECT * 
FROM `users` 
WHERE `user8` LIKE '%Georgia%'
");
$num_rows = mysql_num_rows($result);

echo $num_rows - Georgia\n; 
?>

I would like the MySQL call and the PHP to echo out the lists of all states by percentage.

i.e.
United States 50%
Candana 30%
etc.

This is what I have so far.

<?PHP 
mysql_select_db("freeskat_promail") or die(mysql_error());
$result = mysql_query("SELECT user8, COUNT(*) AS Total, SUM(in) AS TotalIn, SUM(in)*100/COUNT(*) AS Percent FROM lm_users GROUP BY user8
");
$num_rows = mysql_num_rows($result);

echo $num_rows \n; 
?>

It should work, but doesn't.

Any help would be appreciated.
Thanks

use subquery to get total records and then calculate percent by using count of data for each state using group by clause.

SELECT user8, concat((count( * ) *100 / (SELECT count( * ) FROM `lm_users`)) ,  "%") AS percent FROM `lm_users` GROUP BY user8

This is good.

But, trying to round up % to 0 decimal point.
i.e.

Florida 3% not 3.3317%

Something like...

round(percent,0) as rnd FROM user8

Also I'm getting the error...

Warning: Division by zero in /

I'm not echoing it correctly, I'm sure.

My code...

$result = mysql_query("SELECT user8, concat((count( * ) *100 / (SELECT count( * ) FROM lm_users)) , "%") AS percent FROM lm_users GROUP BY user8
");
$num_rows = mysql_num_rows($result);

echo "<FONT COLOR=\"#CCCCCC\">   $num_rows -\n</FONT>"; ?>

Thanks so much for your help.

You are trying to add round on string as i am concatenating it with %.So try by adding round before concat method,it sould work.

SELECT state, concat(round(count( * ) *100 / (SELECT count( * ) FROM `test123`)) ,  "%") AS percent FROM `test123` GROUP BY state

This is good. Thank you.

If you don't mind, just another question.

I have the data displaying on the page, however I need an "IF" statement and I've tried several conditions. None are working.

I do not want to echo/show data that has 0%. Just data that has at least 1%.

Here is the code:

$result = mysql_query("SELECT user8, concat(round(count( * ) *100 /
(SELECT count( * ) FROM lm_users)) , \"%\") AS percent
FROM lm_users GROUP BY user8");
while ($row = mysql_fetch_array($result))

for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
echo "<br>";

Thanks so much for your help