Gossamer Forum
Home : General : Perl Programming :

counting values in multiple fields

Quote Reply
counting values in multiple fields
ok i have a database that has 18 fields for which i want to count values (golf scorecards). each field contains the score one hole while each record is one round of golf for someone. i want to count the number of times each score was made on each hole. sample records:
Code:
5:6:8:3:6:4:8:4:5:6:6:6:6:3:6:6:6:7
5:6:8:3:6:4:8:4:5:6:6:6:6:3:6:6:6:7
5:5:7:3:5:6:6:3:5:6:5:7:6:3:6:6:5:6
6:5:6:4:4:5:7:3:8:7:5:6:8:4:7:6:6:6
so for the above, i would want to know :
hole # 1 had 3 rounds with score of 5 and 1 round with score of 6
hole # 2 had 2 rounds with score of 6 and 2 rounds with score of 5
hole # 3 had 2 round with score of 8 (ugh!), 1 round with score of 7, and 1 round with score of 6
etc

my brain hurts from thinking about this!
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
Hi,

Just trying to get my head around how you want this to work. How do you break up the 4 different games in each row?

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] counting values in multiple fields In reply to
in reality there are several hundred games; i just listed four as an example. not sure i understand your question. for this purpose, i don't care about the player or date or record number, which could be used for other reports to distinguish the records. i just want a count of the number of each score for each hole.

using previous example:
hole # 1 had 3 rounds with score of 5 and 1 round with score of 6
hole # 2 had 2 rounds with score of 6 and 2 rounds with score of 5
hole # 3 had 2 round with score of 8 (ugh!), 1 round with score of 7, and 1 round with score of 6

i guess my final result / report could look like this:
Code:
Hole -> 1 2 3 ... 18
Score:
1 0 0 0 etc
2 0 0 0
3 0 0 0
4 0 0 0
5 3 2 0
6 1 2 1
7 0 0 1
8 0 0 2
etc
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
i found some code in an old app that i think could be modified to do what i want:

Code:
open (DB, "<$db_file_name") or &cgierr("unable to open $db_file_name. Reason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB> ) {
next if /^#/;
next if /^\s*$/;
$line = $_;
chomp ($line);
@fields = &split_decode ($line);
if ($fields[20] eq 'Yes') {
++$count{$fields[$fieldnum]};
}
}
close DB;
this one just counted one value (Yes), where i need to count 10 values (10 is max, 1 is min). it also counted just one field value, where i need to count 18!
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
Hi,

OK, I think I get what you are asking for. Just trying to get my head around the best way to do it as well =) (already done a few versions, but none get the output I want ;))

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
Hi,

OK, well its still a work in progress. I need to call it a day now though, as I'm off out for dinner. So try this as a test script:

Code:
my $string = qq|5:6:8:3:6:4:8:4:5:6:6:6:6:3:6:6:6:7
5:6:8:3:6:4:8:4:5:6:6:6:6:3:6:6:6:7
5:5:7:3:5:6:6:3:5:6:5:7:6:3:6:6:5:6
6:5:6:4:4:5:7:3:8:7:5:6:8:4:7:6:6:6|;

my @games = split /\n/, $string;
my $overall_game_scores = {};

for (my $game_row = 0; $game_row <= $#games; $game_row++) {

my @tmp = split /:/, $games[$game_row];
foreach $hole (0..17) {
my $nice_hole = ($hole+1);

my $foo = $overall_game_scores->{$nice_hole}->{$tmp[$hole]};
print qq|Got $tmp[$hole] on hole $nice_hole\n|;
print qq|\$overall_game_scores->{| . $nice_hole . qq|}->{| . $tmp[$hole] . qq|}\n\n|;
if ($overall_game_scores->{$nice_hole}->{$tmp[$hole]}) {
print qq|increase...\n|;
$overall_game_scores->{$nice_hole}->{$tmp[$hole]}++
} else {
print qq|start...\n|;
$overall_game_scores->{$nice_hole}->{$tmp[$hole]} = 1
}
}

}

use Data::Dumper;
print Dumper($overall_game_scores);

You should see output like so:

Code:
$VAR1 = {
'11' => {
'6' => 2,
'5' => 2
},
'7' => {
'6' => 1,
'8' => 2,
'7' => 1
},
'17' => {
'6' => 3,
'5' => 1
},
'2' => {
'6' => 2,
'5' => 2
},
'1' => {
'6' => 1,
'5' => 3
},
'18' => {
'6' => 2,
'7' => 2
},
'16' => {
'6' => 4
},
'13' => {
'8' => 1,
'6' => 3
},
'6' => {
'6' => 1,
'4' => 2,
'5' => 1
},
'3' => {
'6' => 1,
'8' => 2,
'7' => 1
},
'9' => {
'8' => 1,
'5' => 3
},
'12' => {
'6' => 3,
'7' => 1
},
'14' => {
'4' => 1,
'3' => 3
},
'15' => {
'6' => 3,
'7' => 1
},
'8' => {
'4' => 2,
'3' => 2
},
'4' => {
'4' => 1,
'3' => 3
},
'10' => {
'6' => 3,
'7' => 1
},
'5' => {
'6' => 2,
'4' => 1,
'5' => 1
}
};


The part I was just working on, was to spit out the data in a nice value. As you can see from here, it now show game 1 as having 1 instance of 6, and 3 instances of 5.

Code:
'1' => {
'6' => 1,
'5' => 3
},

OK,I really need to go now. Will come back to it tomorrow with a fresh mind =)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] counting values in multiple fields In reply to
it's working! i just needed to gather the data. i can do the html formatting. thank you so much!!!!
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
No problem - was a fun challenge Angelic

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Post deleted by delicia In reply to
Quote Reply
Re: [Andy] counting values in multiple fields In reply to
here's my report!
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
Was there meant to be anything attached? :)

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] counting values in multiple fields In reply to
guess it didn't upload. will try again
Quote Reply
Re: [delicia] counting values in multiple fields In reply to
haha yeah, the upload of files is a bit weird on here :) (having to press the upload button, and then submitting).

It looks good!

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!