how do i make SQL do the calculations and return the total.

[Jann Linder]

Hello All...

I want to make SQL do all the work here of the calculation (there are many
many entries in the table for the company code.  How do i convert the perl
(inside the while loop below) to a SQL call which does the calculation?

I have already immeasurably sped up the routine by using references...

It has GOT to be faster to make MySQL do the calcs, right?

any ideas?
TIA

Jann

s[u]b get_billables{

 # AC is the Account Code

 my $AC = shift;

 # open connection to database

 my $totalb;
 
 my $db_username = "zyx";

 my $db_password = "xyz";

 my $data_source = "dbi:mysql:database=billing;host=localhost";

 my $dbh = DBI->connect($data_source, $db_username, $db_password) || die
$DBI::errstr;

 my $sqlstr=qq/SELECT rate, hours, discount FROM billable WHERE company =
'$AC'/;

 my $sth = $dbh ->prepare($sqlstr);

 if (!$sth->execute) {
  print "ERROR:" .$sth->errstr."\n";
  die "ERROR:" .$sth->errstr."\n";
  }

 my @Column_References=(\$rate,\$hours,\$discount);

 my $rv = $sth ->bind_columns(undef, @Column_References);


 while ($sth->fetch) {

  #$discount is the discount
  # (in numbers such as 25, 50, 75 representing percentages)

  $totalb += (($rate * $hours) * ((100-$discount) * .01));
  }

 $sth->finish;

 $dbh->disconnect;
 
 return $totalb;
 
 }

--
Jann Linder
Web Developer
Perl Programmer

Personal Site: http://www.jann.com/
CalendarPlus Site: http://www.calendarplus.com/