Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-07-2010
08:04 PM

07-07-2010
08:04 PM

Hello,

I am really missing something here, and I hope someone can help me. I am simply trying to divide two numbers, and end up with a percentage. All I get as a result is 1.0000000

Here is the code I am trying to run:

Select Cast(Field1 / Field2 as Decimal (9,8))

Field1 and Field2 are defined as Decimal(12,0). They have the value of 18321 and 24973 repectively. What I would expect as a result is 0.73363232, but what I get is 1.00000000

What am I missing?

Thanks!

Paul

I am really missing something here, and I hope someone can help me. I am simply trying to divide two numbers, and end up with a percentage. All I get as a result is 1.0000000

Here is the code I am trying to run:

Select Cast(Field1 / Field2 as Decimal (9,8))

Field1 and Field2 are defined as Decimal(12,0). They have the value of 18321 and 24973 repectively. What I would expect as a result is 0.73363232, but what I get is 1.00000000

What am I missing?

Thanks!

Paul

9 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-07-2010
11:25 PM

07-07-2010
11:25 PM

Hi Paul,

there are some rules regarding calculations and rounding on decimals.

precision:

- dec(n1,m1) / dec(n2,m2): the resulting datatype has a precision of max(m1,m2), same rule for +,-,MOD

- dec(n1,m1) * dec(n2,m2): the resulting datatype has a precision of (m1 + m2)

- there's no higher precision kept internally

rounding:

- intermediate results are rounded after each step

Thus "18321 / 24973" results in a rounded 1 which is then casted to 1.00000000.

Try

Select Cast(Field1 as Decimal (18,8)) / Field2

And be aware of the "rounded after each step":

Select 100 * Cast(Field1 as Decimal (18,8)) / Field2

vs.

Select Cast(Field1 as Decimal (18,8)) / Field2 * 100

To avoid that the general rule of thumb is: first multiply then divide.

Dieter

there are some rules regarding calculations and rounding on decimals.

precision:

- dec(n1,m1) / dec(n2,m2): the resulting datatype has a precision of max(m1,m2), same rule for +,-,MOD

- dec(n1,m1) * dec(n2,m2): the resulting datatype has a precision of (m1 + m2)

- there's no higher precision kept internally

rounding:

- intermediate results are rounded after each step

Thus "18321 / 24973" results in a rounded 1 which is then casted to 1.00000000.

Try

Select Cast(Field1 as Decimal (18,8)) / Field2

And be aware of the "rounded after each step":

Select 100 * Cast(Field1 as Decimal (18,8)) / Field2

vs.

Select Cast(Field1 as Decimal (18,8)) / Field2 * 100

To avoid that the general rule of thumb is: first multiply then divide.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-08-2010
06:08 AM

07-08-2010
06:08 AM

Thanks Dieter! That worked great! It was driving me crazy trying to figure out this issue, which is relativly simple.

Paul

Paul

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
04:04 AM

01-13-2012
04:04 AM

Hi,

Can you please help me on this.

How to find the cumultaive sum.

Ex:-I have a coulmn A which has 20 values.at the end i need sum of all the values. and each value stating from 1 should devide by sum...can any one write a sample query.Thank you

Sree

Can you please help me on this.

How to find the cumultaive sum.

Ex:-I have a coulmn A which has 20 values.at the end i need sum of all the values. and each value stating from 1 should devide by sum...can any one write a sample query.Thank you

Sree

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
04:05 AM

01-13-2012
04:05 AM

Percentage what i mean here

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
05:22 AM

01-13-2012
05:22 AM

select col_group, column_a * 100.0000 / sum(column_a) over (partition by col_group rows)

from table

check the SQL manual on orderd analytical functions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
05:24 AM

01-13-2012
05:24 AM

Seems you want an OLAP function:

select

sales_date,

sales_amount,

sum(sales_amount)

over (order by sales_date

rows unbounded preceding) as cumulative_sum

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
06:28 AM

01-13-2012
06:28 AM

Hi Dieter

Thanks for your inputs.

After getting the cumulative sum I want this by percentage like Percentage=col1...col2......column n/cumulative sum

Thanks for your inputs.

After getting the cumulative sum I want this by percentage like Percentage=col1...col2......column n/cumulative sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2012
10:23 AM

01-13-2012
10:23 AM

100 * sales_amount / cumulative_sum?

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-17-2012
12:15 AM

01-17-2012
12:15 AM

yes Dieter