Chris W wrote:
> 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?
>>
>
> What does a show create table give for the price column? I bet it is
> varchar. The only way to make it work then would be to trim off the
> dollar sign and cast it to a float or double.
>
It's a char(20) and NULL in the table titles. I removed the $ and
reloaded and it now works properly. I suspect an ealier version of mysql
had some way to do this. I'm learning that you store a simple number.
But you can add a $ when you select a view.
Karl
