[SOLVED] Ratio problem

[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 A (listed) or B (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) between the total of listed and the total of users.

 

EXAMPLE :

 

listed :

120 - School C

80 - School A

50 - School B

30 - School D

 

users :

40 - School A

10 - School B

10 - School C

5 - School D

 

I want to obtain :

 

50.00% - School A

20.00% - School B

16.66% - School D

8.33% - School C

 

This is very tricky for me!

Many thanks in advance for any solution.

[chocopi]

wouldnt you something like

 

$listed = 100;

$users = 50;

$percent = 100/($listed/$users);

[Germaris]

Thanks for replying!

But I don't figure out how to format your solution...

 

Maybe it's because of a lack of precision in my code sample which must be read as follow:

(have a look to the //sentence)

 

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

 

Could you please develop your reply?

Thank you!

[chocopi]

what variables are the numbers stored in for listed and users ???

[Germaris]

Numbers of students, of course.

[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 http://forums.devshed.com/php-development-5/ratio-problem-479828.html#post1909140

[Barand]

If your tables have something like a studentID column to join on, you could maybe do it in a more efficient query with a LEFT join.

[Rithiur]

If your tables have something like a studentID column to join on, you could maybe do it in a more efficient query with a LEFT join.

That is not really possible, because you have two completely different data sets, which are not connected. They may have same user values, but the problem is that we are not combining those rows here, we are trying to compare the amount of rows, so you have to do with a "tricky" subquery query like that.

 

Imagine, if you have data sets like

 

School | userid

foo | 1

foo | 2

foo | 3

bar | 4

bar | 5

 

school | userid

foo | 1

foo | 3

bar | 5

 

Now, you need to compare the number of 'bar's you have in the second data set compared to the number 'bar's on the first data set. You can't really join on user id, since you only would get the "bar 5" value, which would not make much sense.

[Germaris]

... which are not connected. They may have same user values, but the problem is that we are not combining those rows here, we are trying to compare the amount of rows...

 

This is right. And, more, my host has blocked the use of linked tables...

 

[Barand]

If your tables have something like a studentID column to join on, you could maybe do it in a more efficient query with a LEFT join.

That is not really possible, because you have two completely different data sets, which are not connected. They may have same user values, but the problem is that we are not combining those rows here, we are trying to compare the amount of rows, so you have to do with a "tricky" subquery query like that.

 

Imagine, if you have data sets like

 

School | userid

foo | 1

foo | 2

foo | 3

bar | 4

bar | 5

 

school | userid

foo | 1

foo | 3

bar | 5

 

Now, you need to compare the number of 'bar's you have in the second data set compared to the number 'bar's on the first data set. You can't really join on user id, since you only would get the "bar 5" value, which would not make much sense.

 

So from your data above

- foo has 3 listed and 2 users (66.666%)

- bar has 2 listed and 1 user (50%)

 

So if we LEFT JOIN on student

 

SELECT l.school, COUNT(*) as listed,
  SUM(IF(u.studentID IS NOT NULL,1,0)) as users,
  SUM(IF(u.studentID IS NOT NULL,1,0))*100/COUNT(*) AS pcent
FROM sch_listed l LEFT JOIN sch_user u ON l.studentID = u.studentID
GROUP BY l.school

 

we get -->[pre]

school |  listed  | users  |  %

-------+----------+---------+--------+

  bar  |    2    |  1    | 50.0000

  foo  |    3    |  2    | 66.6667

[/pre]

 

That is not really possible

You were saying.?

[Rithiur]

You were saying.?

As I was saying: Thanks for MySQL lesson. (pardon my stupidity about LEFT JOIN)

[Germaris]

Barand, you're simply BRILLIANT !!!

Nice syntax...

I'll give it a try.

Cheers!

[Barand]

This is right. And, more, my host has blocked the use of linked tables...

 

If that's the case I'd cancel my payments. A relational DB without joins is a bicycle without wheels.