Calculation from DB Results

[mcmuney]

I'm using a code to display the count of males vs females. What I'd like to do next is show the data in a table format, showing:

Gender Count %
Female    5    56%
Male      4    44%
Total      9    100%

How do I perform the total and % calculations from what I have now:
[code]
<?php
// Make a MySQL Connection

$query = "SELECT scm_gender, COUNT(scm_gender) FROM sc_member GROUP BY scm_gender";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(scm_gender)'] ." ". $row['scm_gender'] ." items.";
echo "<br />";
}
?>[/code]

[akitchin]

you'll probably need to pull the totals first, and then run each gender (unless an SQL guru can slot it into one query):

[code]<?php
// grab the total count
$total_query = "SELECT COUNT(scm_gender) FROM sc_member";
$res = mysql_query($total_query) or die(mysql_error());
$total_count = mysql_result($res, 0, 0);

// start the table
echo '<table><tr><td>Gender</td><td>Count</td><td>Percentage</td></tr>';

// go through each gender (lets hope you only have two)
$gender_query = "SELECT scm_gender, COUNT(scm_gender) AS gender_count FROM sc_member GROUP BY scm_gender";
$res2 = mysql_query($gender_query) or die(mysql_error());
while ($info = mysql_fetch_assoc($res2))
{
  echo '<tr><td>'.$info['scm_gender'].'</td><td>'.$info['gender_count'].'</td><td>'.round(100 * ($info['gender_count'] / $total_count)).' %</td></tr>';
}

// finish the table with the total
echo '<tr><td>Total</td><td>'.$total_count.'</td><td>100 %</td></tr></table>';
?>[/code]

tidy up the HTML as you see fit.

[hitman6003]

I think this will work:

[code]SELECT count(scm_gender) as total, count(scm_gender = 'male') as Male, count(scm_gender = 'female') as Female FROM sc_member;[/code]

But I haven't tested it.

EDIT:  I don't think it'll work, I tried it on a table I have in my db and it did not.