[SOLVED] Average out of 10

[Canman2005]

Hi all

 

Wondering if someone can help.

 

I have the following query

 

$sql = "SELECT * FROM `levels`";
$show = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($show))
{
print $row['levelcode'].'<br>';
}

 

which returns a list of level codes, the output at the moment looks like

 

72

234

4

7655

43

 

What I want to do is display next to each code returned, an average score out of 10 compared to the other codes.

 

I don't 100% know to write the query or even what the result should be, but it would look something like

 

72 - (2.6)

234 (7.4)

4 (0.4)

7655 (9.9)

43 (1.4)

 

Of course those numbers are incorrect, hence why I am trying to work out the query to use.

 

I thought maybe it would be something like

 

 

select count(*) as cnt, SUM(levelcode) as sum, (SUM(levelcode)/count(*)) as avg FROM levels;

 

but that doesnt stick to an average of 10, plus it gives an average for the whole database and not each row individually, plus it gives 4 digits result after the fullstop, such as 27.9375, whereas I need to stick to 1 digit such as 5.4

 

Could someone please help me work this one out, it's slowly making me dumber

 

Thanks

 

Dave

[DarkWater]

I don't quite understand what you mean.  You want it the percentage relative to the other values?  Should it total to 10%?

[Canman2005]

Yes, I think that method would work, how would it be possible to do with the top value being 10%

[DarkWater]

Hmm...can you say why you're trying to do this and elaborate a bit more?  I'm thinking of a way but I'm not too sure it'll work.

[Canman2005]

 

Well I guess im just trying to get a score out of 10 depending on their value compared to other in the table

[Canman2005]

any ideas?

[discomatt]

Some accurate sample data would be ideal. It's hard to help you when we don't know what you want.

 

Example:

 

Source Data

200
150
90
50
10

 

Expected Results

200 (10)
150 (7.5)
90 (4.5)
50 (2.5)
10 (0.5)

 

 

The algorithm I'm assuming you're gonna need is something like this

 

$base = 10;

$ratio = $base / max($values);

foreach ( $values as $value )
   echo $value .' ('. ( $value * $ratio ) .')<br />';

[DarkWater]

I was in my pool. xD  Sorry.  And yeah, discomatt has it right according to what you wanted.

[akitchin]

alternatively, if you're simply looking for the proportion the number represents from the whole (rather than comparing it to the max value), you can do the following:

 

SELECT levelcode, levelcode/SUM(levelcode) AS fraction FROM levels

 

to get the fraction of ten it represents, simply multiply that decimal by 10:

 

while (stuff = stuff)
{
  echo $stuff['levelcode'].' - '.round($stuff['fraction'] * 10, 2).'<br />';
}

 

i have to caution that i don't know whether MySQL will like the use of an aggregate function without a group by, but it's worth a shot.  if MySQL complains, run the levelcode query first and store in an array, then calculate the sum of the array and echo much like disco has suggested.

[Canman2005]

discomatt, what query should I use with that?

 

akitchin, your query wants me to do a GROUP and if I do that, it doesnt seem to add up multiple values assigned to that userid

 

any ideas?

[akitchin]

SELECT levelcode FROM levels

$levels = array();
while (stuff = stuff)
{
  $levels[] = $stuff['levelcode'];
}

$total = array_sum($levels);
foreach ($levels AS $value)
{
  echo $value.' = '.round($value / $total, 2).'<br />';
}

 

give that a whirl.  of course, you'll need to make it syntactically correct.

[Canman2005]

Hi all

 

I tried altering it to

 

$stuff  "SELECT levelcode FROM levels";
$levels = array();
while ($stuff = $stuff)
{
  $levels[] = $stuff['levelcode'];
}

$total = array_sum($levels);
foreach ($levels AS $value)
{
  echo $value.' = '.round($value / $total, 2).'<br />';
}

 

not sure if I did that right, but it doesnt seem to produce any result at all.

 

Can anyone help?

[Canman2005]

any ideas? still at it with no luck yet

[DarkWater]

Read the while loop condition and tell me if it makes sense. =P  You also never even execute the query.

 

while ($stuff = $stuff) {

 

xD  Change it to:

 

$sql = "SELECT levelcode FROM levels";

$result = mysql_query($sql) OR die(mysql_error());

$levels = array();

while ($row = mysql_fetch_assoc($result))

{

  $levels[] = $row['levelcode'];

}

$total = array_sum($levels);

foreach ($levels AS $value)

{

  echo $value.' = '.round($value / $total, 2).'<br />';

}

[Canman2005]

Cool, well that produced something, but do you know how to get it to output as 1 didget after the decimal place, so

 

0.04

 

becomes

 

0.4

[Canman2005]

Plus i'm not sure that's producing the right results, discomatt comments with outputting the following as an example looked right

 

200 (10)

150 (7.5)

90 (4.5)

50 (2.5)

10 (0.5)

 

I mean, that's the kinda result I want to product

 

any good ideas anyone?

[Barand]

My data

[pre]

mysql> SELECT x FROM numbers;

+----+

| x  |

+----+

| 20 |

|  2 |

|  8 |

|  4 |

|  5 |

| 21 |

| 25 |

+----+[/pre]

 

<?php
$maxres = mysql_query ("SELECT MAX(x) FROM numbers");
$max = mysql_result ($maxres,0);

$sql = "SELECT x, x*100/$max as pc FROM numbers";
$res = mysql_query($sql);
while (list($x, $pc) = mysql_fetch_row($res))
{
    printf('%d (%0.1f%%)<br/>', $x, $pc);
}			
?>

-->
20 (80.0%)
2 (8.0%)
8 (32.0%)
4 (16.0%)
5 (20.0%)
21 (84.0%)
25 (100.0%)

[Canman2005]

cool, guess I could just devide it by 10 to get a average out of 10

 

or is there a more logical way of doing that

[Canman2005]

How can I remove the % sign from the code above outputs?

[Barand]

remove the "%%"

printf('%d (%0.1f)<br/>', $x, $pc);

[Canman2005]

oh thanks mate.

 

what would be the best way to just display the value, so

 

20 (80.0)

2 (8.0)

8 (32.0)

4 (16.0)

5 (20.0)

 

would be come

 

80.0

8.0

32.0

16.0

20.0

 

and then deviding them by 10

 

is there a prefered way of doing that?

 

[Canman2005]

Managed to get it right, in the end it was

 

printf('% (%0.1f<br>', $x, $pc/10);

 

Thanks for all the help

[Barand]

instead of the divide by 10 on output you could

 

$sql = "SELECT x, x*10/$max as pc FROM numbers";