Decimal formatting

Forum|Forum|16 years ago
December 15, 2009
3 replies
687 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 ?

Getting started

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

BKBK

Correct answer

Community Expert

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

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

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

ilssac

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded