I don´t have a field as such, but I am making a new field which is the result dividing an existing field, i.e. cost/1.15
Is there a way to restrict the result of this calculation to two decimal places?
+1
A:
Sounds like you need the ROUND() function.
Eg. ROUND(cost/1.15, 2)
Blorgbeard
2010-07-03 19:26:00
+2
A:
You could change the column type to a NUMERIC(p, 2) where p is the precision, especially if it is money (I'm guessing from cost that it might be money).
Also making a column which is derived from another column is generally a bad idea as the two can get out of sync. Consider making a view instead.
Mark Byers
2010-07-03 19:32:59
chould : could + should?
Blorgbeard
2010-07-03 19:36:58
Hmm, is decimal more precise than double? Would I use decimal as such DECIMAL(cost/1.15, 2, 2) ?
Jacob
2010-07-03 19:40:09
@Blogbeard: I couldn't decide ;) I've settled for *could*. I was tempted to write *should*, but there is insufficient information in the question to be sure that's what he wants.
Mark Byers
2010-07-03 19:43:26
@Jacob: It's a column type not a calculation. I've updated my answer to make it more clear. Try following the link for more info.
Mark Byers
2010-07-03 19:47:40
@Jacob: Decimal is in general *NOT* more precise than `double`, and much slower than `double`. However, it can *exactly* represent decimal fractions like 0.01, which a binary type cannot. This is why decimal types are recommended for storing amounts of money.
dan04
2010-07-03 19:53:30
I can´t actually make new columns as I am in the confines of a CMS, I can choose columns within the CMS to display and assign them an sql query, but can´t actually create new columns or views..
Jacob
2010-07-03 20:00:15
@Jacob: That's OK then.
Mark Byers
2010-07-03 20:04:27
@mark: so DECIMAL(cost/1.15, 2, 2) would be correct given my constraints?
Jacob
2010-07-03 20:33:30