List: General Discussion « Previous MessageNext Message » From: Brent Baisley Date: June 22 2006 8:32pm Subject: Re: Math problem View as plain text
```dollar/currency/money, call it whatever you like, but if it's got a \$ in front of it, then
it's a string, not a number. Your price
field should have been created as a decimal. I'm guessing it's a char type. You should
also set your fields to default to 0, not
NULL.
The problem is with your setup, not mysql. You shouldn't mix formatting (the \$ sign) with
raw data, which is what you are doing.
What if the price is in euros? Then the \$ is just wrong. Strip out the \$ signs you mysql
may be able to translate it correctly.

----- Original Message -----
From: "Karl Larsen" <k5di@stripped>
To: <mysql@stripped>
Sent: Thursday, June 22, 2006 4:22 PM
Subject: Math problem

>    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?
>
> Karl
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

```