0

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

Edited by Dani: Formatting fixed

2
Contributors
4
Replies
48
Views
4 Years
Discussion Span
Last Post by ourloop
0

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

Edited by IIM: modify

0

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.

0

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
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.