Well that works fine, but I want to put a $ sign in front of every
dollar amount. I will do that but I'm not there yet.
Karl
C.R.Vegelin wrote:
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" <[EMAIL PROTECTED]>
To: "Chris W" <[EMAIL PROTECTED]>
Cc: "MYSQL General List" <mysql@lists.mysql.com>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
