maderman has asked for the wisdom of the Perl Monks concerning the following question:

Hi all. With DBI and Oracle I'm working with a database with the following entries (cat1..cat15 are categories): 
    report_no,
    reported_by
    project_no
    date_reported
    cat1
    cat2
    cat3
    cat4
    cat5
    cat6
    cat7
    cat8
    cat9
    cat10
    cat11
    cat12
    cat13
    cat14
    cat15

[download]
and for categories cat1..cat15, I'd like to get an average for each row AND an average for each column. I'm having problems trying to work out how to get the latter. Here is what I have so far: 
use strict;

$sth = $dbh->prepare(qq{

  SELECT 
    report_no,
    reported_by,
    project_no,
    to_char(date_reported,'DD-MON-YYYY'),
    cat1
    .
    .
    .
    cat15
      FROM TABLE
               ORDER BY report_no DESC
          
      });


$sth->execute();
$array_ref = $sth->fetchall_arrayref();

$i = 0;

foreach $row (@$array_ref) {
  
  ($report_no,$reported_by,$project_no,$date_reported) 
   = @$row[0..3];

  #Categories in each row may have undef elements, replace
  #with a '-'. For defined values, sum them up. This sum is
  #used to calculate the average for each row.
  
  $number = 0;
  $sum = 0;
  foreach $element (@$row[4..18]) {
    if ($element == -1) { $element = '-' }
    else { 
      $element = sprintf "%.2f", $element;
      $sum += $element;
      ++$number;
    }
  }
  
  $row_avge = sprintf "%.2f", $sum / $number;
  
  $i++;
}

$sth->finish();
$dbh->disconnect;

[download]
Can anyone help me with getting averages for each column? I'm all hashed out...
Many thanks in advance.
Stacy.

Replies are listed 'Best First'.
Re: DBI and fetchall_arrayref (gosh!)
by busunsl (Vicar) on Nov 16, 2001 at 13:48 UTC
    I'm not familiar with Oracle, but you should be able to use the compute clause in the select statement: 
    SELECT 
    report_no,
    reported_by,
    project_no,
    to_char(date_reported,'DD-MON-YYYY'),
    cat1, cat2, cat3
compute avg(cat1), avg(cat2), avg(cat3)

    [download]

    If that doesn't work, try this:

    before the first foreach loop:

    my @sums;

    [download]
    In the second loop:

    $sums[$number] += $element;

    [download]
    At the end you have the sum of each column in @sums and just have to divide each sum by $i, which holds the number of rows.
[reply]
[d/l]
[select]
Re: DBI and fetchall_arrayref (gosh!)
by Masem (Monsignor) on Nov 16, 2001 at 16:55 UTC
    I'm not sure if they are standard, but MySQL has 'grouping' operators that work on selects, including average, min, max, and count: 
    SELECT AVERAGE(cat1), AVERAGE(cat2), ... FROM table

    [download]
    You'd run this as a separate query from the one that gets the row averages.

    -----------------------------------------------------
    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
    "I can see my house from here!"
    It's not what you know, but knowing how to find it if you don't know that's important

[reply]
[d/l]
      Hi Michael. Yes there is an average function in Oracle, but the form of my SQL statement would take the form: 
      
$sth = $dbh->prepare(qq{
  
  SELECT 
   avg(DECODE(cat1, -1,null,cat1)),
   .
   .
   .
   avg(DECODE(cat15, -1,null,cat15))
      FROM TABLE
      });

      [download]
      The reason for this monster: well entries in columns can be '-1' (indicating the category was not applicable at entry time). So in order to get statistics, I have to change the -1's to nulls - that way Oracle doesn't include the nulls in the AVG calculations.... Regards, Stacy.
[reply]
[d/l]
Re: DBI and fetchall_arrayref (gosh!)
by runrig (Abbot) on Nov 16, 2001 at 22:31 UTC
    Here's a simple example of averaging columns of data, with your constraints thrown in (ignore and convert undefined and '-1' data):
    my @sum;
my @num;
while (<DATA>) {
  my @data = split;
  $_ = (defined and $_ != -1)? $_ : '-' for @data;
  $sum[$_]+=$data[$_], $num[$_]++
    for grep { $data[$_] ne '-' } 0..$#data
}
print "@{[map { $sum[$_]/$num[$_] } 0..$#sum]}\n";

__DATA__
1 2 3 4
2 3 4 5

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom