Hi Richard, > i want to round() the value of a column, but the number of digits is > depending on the value of another column. i found out, that this doesn't work: > > create table testme > -> (number double (5,4),digits tinyint(3)); > Query OK, 0 rows affected (0.08 sec) > > mysql> explain testme; > +--------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +--------+-------------+------+-----+---------+-------+ > | number | double(6,4) | YES | | NULL | | > | digits | tinyint(3) | YES | | NULL | | > +--------+-------------+------+-----+---------+-------+ > 2 rows in set (0.08 sec) > > mysql> insert into testme values (100.4235,3),(85.4,1); > Query OK, 2 rows affected (0.08 sec) > Records: 2 Duplicates: 0 Warnings: 0 > > mysql> select * from testme; > +----------+--------+ > | number | digits | > +----------+--------+ > | 100.4235 | 3 | > | 85.4000 | 1 | > +----------+--------+ > 2 rows in set (0.08 sec) > > mysql> select round(number,digits) from testme; > +----------------------+ > | round(number,digits) | > +----------------------+ > | 100.4240 | > | 85.4000 | > +----------------------+ > 2 rows in set (0.08 sec) > > > is there any way i could do this with *one* query? > the only solution i see is to read the digits-column with a script and then > create a new query, but thats not possible in my case...
It does the same for me (that's not much help, but it's obviously not 'you'!) I first went looking to see if there is a MySQL parameter which says how many decimal digits will be displayed, by default - but failed to find it and may be confusing myself with another product. Certainly nothing in my.cnf. I then wondered if the reason the result is expressed this way is because of the schema which defines: number double (5,4) Sure enough, changing number to double (5,3) and then re-running your tests, makes it change. Again no help to you. I notice that all of the examples in the manual for round(), truncate(), and format() render the D argument as a constant. So I'm wondering if the "digits" is being ignored? Sorry not to have more to offer. Is there a wiser mind that can shed some light? =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
