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. |
\----------------------------------------------------------------------/
