Hi Karl,
Your question: can I add a $ when you select a view.
I suggest to include $ sign in the field alias, like:
Select title_id, ytd_sales * price AS `Turnover $` From titles;
HTH, Cor
----- Original Message -----
From: "Karl Larsen" <k5di@stripped>
To: "Chris W" <2wsxdr5@stripped>
Cc: "MYSQL General List" <mysql@stripped>
Sent: Thursday, June 22, 2006 10:04 PM
Subject: Re: Math problem
> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
