Skip to main content
T
trojnfn
Inspiring
December 15, 2009
Answered

Decimal formatting

  • December 15, 2009
  • 3 replies
  • 685 views

I have a query that will caculate an average, here is the line of code :

cast(sum(agedDays) as decimal)/cast((sum(days_GT_60) + sum(days_31_60) + sum(days_5_30) + sum(days_LT_5)) as decimal) as averageAging

So if I have agedDays = 130 and the sum of the days = 17, the average is 130/17 = 7.6470588235294117647........

How do I just get the output to be 7.6 (rounded) ?

Would this be done in SQL or do I need to format it in ColdFusion ?

This topic has been closed for replies.
Correct answer BKBK

You might be in luck with

round(cast(sum(agedDays) as decimal)/cast((sum(days_GT_60) + sum(days_31_60) + sum(days_5_30) + sum(days_LT_5)) as decimal), 1) as averageAging

3 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
December 19, 2009

You might be in luck with

round(cast(sum(agedDays) as decimal)/cast((sum(days_GT_60) + sum(days_31_60) + sum(days_5_30) + sum(days_LT_5)) as decimal), 1) as averageAging

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
December 15, 2009

You can use the NumberFormat function in CF to handle formatting.  To handle rounding take a look at the user defined functions available at CFLib.org.

http://www.cflib.org

D
Dan_Bracuk
Inspiring
December 15, 2009

If you use numberformat, you won't need any other rounding functions.

ilssac
ilssac
Inspiring
December 15, 2009

trojnfn wrote:

Would this be done in SQL or do I need to format it in ColdFusion ?

Your choice.  Most, if not all, database management systems have some type of rounding function.

ColdFusion does as well.  Do it where you are most comfortable and|or makes the most sense for your system.

The usual method to round to a 1/10:  (round(number * 10)) / 10.  So you could do that with SQL functions or ColdFusion functions.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices