Re: Math problem - C.R.Vegelin - com.mysql.lists.mysql

8 messages in com.mysql.lists.mysqlRe: Math problem

From	Sent On	Attachments
Karl Larsen	22 Jun 2006 13:21
Chris W	22 Jun 2006 13:30
Jay Blanchard	22 Jun 2006 13:31
Brent Baisley	22 Jun 2006 13:32
Karl Larsen	22 Jun 2006 14:04
Peter Brawley	22 Jun 2006 14:05
C.R.Vegelin	23 Jun 2006 00:48
Karl Larsen	23 Jun 2006 05:24

Subject:	Re: Math problem
From:	C.R.Vegelin (cr.v...@hetnet.nl)
Date:	06/23/2006 12:48:22 AM
List:	com.mysql.lists.mysql

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

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.