The below code displays correctly, I need an "IF" statement that prevents displaying rows with 0% in the percent column and I've tried several conditions. None are working.

I only want to display rows that has at least 1% in the percent column.

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>";

Any help would be appreciated.

Recommended Answers

All 3 Replies

What about adding something like WHERE percent > 0 to your statement? :)

On a side note: I'm not sure if this is an efficient query. I'm not a MySQL expert, but for as far as my knowledge goes I think this query is executing its subquery a lot of times (each time a record is fetched, it has to execute the subquery). Why don't you just make two queries? One fetching the maximum COUNT from lm_users, and then one that fetches all the records. You could use PHP to do the percentage calculations. Anyone here with more information on this?

its very simple ,use having clause.Have a look at http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

SELECT user8, concat(round(count( * ) *100 / (SELECT count( * ) FROM `lm_users`))  ,  "%") as  percent FROM `lm_users`  GROUP BY user8 having percent>0

Please mark thread as solved if your problem is solved.

Woops, I meant HAVING, of course, not WHERE :).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.