Jump to content


Photo

Calculation from DB Results


  • Please log in to reply
2 replies to this topic

#1 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 18 July 2006 - 09:05 PM

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:
<?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 />";
}
?>


#2 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 18 July 2006 - 09:27 PM

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

<?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>';
?>

tidy up the HTML as you see fit.

#3 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 18 July 2006 - 09:35 PM

I think this will work:

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

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users