How could one add the information from the many side of a databse
If on the many side database I have 10 records with the same id how could I get a sum of a field of those 10 records and divide by the number of records.

Try the following sub-routine:
Then call it using &get_average("NUMBER_OF_FIELD_TO_AVERAGE");

Good luck!

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

Do I put this in the one side .pl the data that I need is in the many side db. If there is 10 records of one id in the many side of the database this will add up the field and divide by the number of records, if there is 3 records of another id give me the average if these are we on the same track?

Oh I think I understand... you want to find out what average of your "many" database has (for example) "10" as the ID, right?

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

Correct if the id is 10 and is in the many side 10 times I want the average of a rating field

ie

idfield ratingfield
10 12
10 13
10 14
10 12
10 11
10 12
10 16
10 17
10 18
10 14

So the number i would want is 139/10 = 13.9 it just so happens there was 10 instances of this id.

And I would like the results in the short display

Ok, cool, I get it now

If we tweak the above sub a bit...
Now call it using &get_average("ID_TO_AVERAGE");

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

Do I have to do any database switching or just plug the code into the one side .pl file?

You can just plug it in!

I did make one quick change to the code above to account for this situation, so just copy the code again (sorry if you already had)

The 3 things you need to customise:
* FIELD_NUMBER_OF_ID_FIELD
* FIELD_NUMBER_OF_RATING_FIELD
* many.db (The database to open)

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

you say
Now call it using &get_average("ID_TO_AVERAGE");

what is id to average?

If you want to display the average of all ratings with an ID of "10", then you would use &get_average("10");

You can use either hard numbers like above or a variable (&get_average("$a_field");). As long as the value is a number, it should all work.

This could also be turned into a form interface... (eg, show me the average of all ratings with the ID of [X])

The possibilities are endless

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

Hmm

this is what I have in the short display

<td valign=top><font size="-4">&get_average("$Playerid")</font></td>

what am I missing

You'll need to stop prinitng momentarily, stop any print qq| statements you might have, then call the sub. eg:

<td valign=top><font size="-4">|;
&get_average("$Playerid");
print qq|</font></td>


- Mark

Astro-Boy!!
http://www.zip.com.au/~astroboy/

Ok getting better now getting this error

fatal error: Illegal division by zero at ./player.pl line 595.

In other words, the script thinks there are no records with that particular ID. Is this true?

Either way, we should add checking for that kind of thing (add the red lines):

if ($avg_count > 0) {
$avg_total = int $avg_total / $avg_count; # Average the total by number of records
} else { $avg_total = 'No matches for your ID found'; }
print $avg_total;

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/

I truly appreicate your help thanks it works great.

Just want to ask you something

In the sub_html_record

I have this after my (%rec) = @_;

&switch_to_rating;
%rec2 = &get_record($rec{$db_key});
# $grand_total += $rec2{'Autorate'};
&switch_to_player;


and to get it to look into the proper database I changed your above code to this

<td valign=top><font size="-4">|;&get_average("$rec2{'Playerid'}");print
qq|</font></td>

Is this correct it works but i just want to make sure

Jason

That looks fine, glad we got it working

Cheers,

- Mark


Astro-Boy!!
http://www.zip.com.au/~astroboy/