How to get the ratio ?

Germaris

Greetings!

I manage a directory of former students of different schools.
The interface is a Flash (.swf) file to which I send a Flash String.

For each school, I display various searches (by name, promotion, geographical location, etc).

I also display in a "Hall of Fame" the rankings related:
a - to the total of students listed for each school.
b - to the total of registered students using the directory.
(code below)

<?php
include "key.php";
// $table may be $table_one (listed) or $table_two (users)
$query = "SELECT school, COUNT( * ) AS n FROM $table GROUP BY shool HAVING ( n > 0 ) ORDER BY n DESC";
$res = mysql_query($query) or die("&error=Impossible to connect");
while (list($school, $n) = mysql_fetch_row($res)) {
	$flashstr .= "$n"." - "."$school"."<br />";
}
print "&fulllist=".urlencode($flashstr);
?>

I wish to display another new ranking based upon the "ratio" in percentage (FOR EACH SCHOOL) obtained by comparing the results of listed and users.

EXAMPLES :

listed :
120 - School C
80 - School A
50 - School B
30 - School D
etc...

users :
40 - School A
10 - School B
10 - School C
5 - School D
etc...

I want to obtain :

50.00% - School A
48.00% - School E
20.00% - School B
16.66% - School D
8.33% - School C
7.00% - School H
3.00% - School W
etc...

For a better understanding of my problem, please see the sample of what I obtain by applying my queries to table_one and table_two at this link

I want to obtain the ratio FOR EACH SCHOOL of users (green numbers) vs listed (red numbers) and then order those ratios by descending order.

This is very tricky for me!
Many thanks in advance for any solution.
Best regards.

Gerry

halojoy

create 2 arrays, with values for each school, listed() and users()
$listed[$school]=n;
$users[$school]=n;
make a foreach loop calculate percent, and put it into array $ratio[$school]

<?php

foreach( $users AS $school => $n ){
// round to 2 decimals
$ratio[$school] = round(100 * $users[$school] / listed[$school], 2);
}

// you can simply sort  array $ratio by percent values if you wish
sort( $ratio );

foreach( $ratio AS  $school => $perc ){
echo $perc .' - '. $school;
}

?>

Germaris

Thank you ror replying !

Before I give your solution a try, please go to this link and tell me if you well understood the problem and, therefore, if you maintain your solution.

Thanks again for the time you dedicate to help me !!!

Germaris

<?php
SELECT u.school, CONCAT(((u.n / l.n) * 100), ' %') as ratio 
FROM* 
(SELECT school, COUNT( * ) AS n FROM table_users GROUP BY school HAVING ( n > 0 ) ORDER BY n DESC) as u 
LEFT JOIN 
(SELECT school, COUNT( * ) AS n FROM table_listed GROUP BY school HAVING ( n > 0 ) ORDER BY n DESC) as l 
on l.school=u.school 
ORDER BY (u.n / l.n) DESC;
?>

Solution given by TonyF123 at this link