From: C.R.Vegelin
Date: June 23 2006 7:48am
Subject: Re: Math problem
List-Archive: http://lists.mysql.com/mysql/199223
Message-Id: <006701c69699$79ee2250$0100000a@your0j166cfae3>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

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=cr.vegelin@stripped
>
>