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.

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.

United States 50%
Candana 30%

This is what I have so far.

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.

Edited by Dani: Formatting fixed

5 Years
Discussion Span
Last Post by ourloop

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


This is good.

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

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

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.