From: Peter Brawley
Date: June 22 2006 9:05pm
Subject: Re: Math problem
List-Archive: http://lists.mysql.com/mysql/199211
Message-Id: <449B0623.2060402@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >It appears that mysys 4.1 does not know how to multiply a dollar
 >amount to another number. Has anyone else seen this problem?

If price is a string beginning with '$', MySQL will autoconvert 
SubString(price,2) to a numeric iif it's in a numeric expression, but 
you'd be much better off losing the leading '$' chars and converting the 
column to Decimal.

PB


Karl Larsen wrote:
>    I'm trying to multiply numbers one of which is money. The money 
> looks like this:
>
> SELECT price FROM titles;
>
> | price  |
> +--------+
> | $20.00 |
> | $19.99 |
> | $7.99  |
> | $19.99 |
> | $11.95 |
> | $19.99 |
> | $14.99 |
> | $11.95 |
> | $22.95 |
> | $2.99  |
> | $10.95 |
> | $7.00  |
> | $2.99  |
> | $20.95 |
> | NULL   |
> | $19.99 |
> | $21.59 |
> | NULL   |
> +--------+
> 18 rows in set (0.01 sec)
>
> When I use SELECT title_id, ytd_sales * price From titles;
>
> I get:
> | title_id | ytd_sales | price * ytd_sales |
> +----------+-----------+-------------------+
> | PC8888   |      4095 |                 0 |
> | BU1032   |      4095 |                 0 |
> | PS7777   |      3336 |                 0 |
> | PS3333   |      4072 |                 0 |
> | BU1111   |      3876 |                 0 |
> | MC2222   |      2032 |                 0 |
> | TC7777   |      4095 |                 0 |
> | TC4203   |     15096 |                 0 |
> | PC1035   |      8780 |                 0 |
> | BU2075   |     18722 |                 0 |
> | PS2091   |      2045 |                 0 |
> | PS2106   |       111 |                 0 |
> | MC3021   |     22246 |                 0 |
> | TC3218   |       375 |                 0 |
> | MC3026   |      NULL |              NULL |
> | BU7832   |      4095 |                 0 |
> | PS1372   |       375 |                 0 |
> | PC9999   |      NULL |              NULL |
> +----------+-----------+-------------------+
> 18 rows in set (0.04 sec)
>
>    It appears that mysys 4.1 does not know how to multiply a dollar 
> amount to another number. Has anyone else seen this problem?
>
> Karl
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.2/372 - Release Date: 6/21/2006