List: | General Discussion | « Previous MessageNext Message » | |

From: | C.R.Vegelin | Date: | June 23 2006 7:48am |

Subject: | Re: Math problem | ||

View as plain text |

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 > >

Thread | ||
---|---|---|

• Math problem | Karl Larsen | 22 Jun |

• Re: Math problem | Chris W | 22 Jun |

• Re: Math problem | Karl Larsen | 22 Jun |

• Re: Math problem | Brent Baisley | 22 Jun |

• Re: Math problem | Peter Brawley | 22 Jun |

• Re: Math problem | C.R.Vegelin | 23 Jun |

• Re: Math problem | Karl Larsen | 23 Jun |

• RE: Math problem | Jay Blanchard | 22 Jun |