List:General Discussion« Previous MessageNext Message »
From:Karl Larsen Date:June 22 2006 9:04pm
Subject:Re: Math problem
View as plain text  
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

Thread
Math problemKarl Larsen22 Jun
  • Re: Math problemChris W22 Jun
    • Re: Math problemKarl Larsen22 Jun
  • Re: Math problemBrent Baisley22 Jun
  • Re: Math problemPeter Brawley22 Jun
  • Re: Math problemC.R.Vegelin23 Jun
    • Re: Math problemKarl Larsen23 Jun
RE: Math problemJay Blanchard22 Jun