List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:May 1 1999 7:59pm
Subject:Re: how do i make SQL do the calculations and return the total.
View as plain text  
Jann Linder wrote:
> 
> 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?

[snip]

>  my $sqlstr=qq/SELECT rate, hours, discount FROM billable WHERE company =
> '$AC'/;
> 
>  my $sth = $dbh ->prepare($sqlstr);
[snip]
>  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));
>   }

Untested, but I would imagine you'd use something like this:

SELECT company,SUM((rate * hours) * ((100-discount)/100)) FROM billable
GROUP BY company

I don't know if MySQL can do SUMS on non-column quantities, but there's
only one way to find out.

Jules

/----------------+-------------------------------+---------------------\
|  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
|  Jules aka     |                               |  Richmond, Surrey   |
|  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
+----------------+-------------------------------+---------------------+
|  War doesn't demonstrate who's right... just who's left.             |
|  When privacy is outlawed... only the outlaws have privacy.          |
\----------------------------------------------------------------------/
Thread
how do i make SQL do the calculations and return the total.Jann Linder1 May
  • Re: how do i make SQL do the calculations and return the total.Jules Bean1 May