Calculation from DB Results

2 replies to this topic

#1 mcmuney

mcmuney
• Members
• 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
• 2,516 posts

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
• 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