Database

## Creating a Percentage

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
Tags (3)
9 REPLIES
Senior Apprentice

## Re: Creating a Percentage

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

## Re: Creating a Percentage

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

Paul
Enthusiast

## Re: Creating a Percentage

Hi,

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
Enthusiast

## Re: Creating a Percentage

Percentage what i mean here
Supporter

## Re: Creating a Percentage

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

Senior Apprentice

## Re: Creating a Percentage

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

Enthusiast

## Re: Creating a Percentage

Hi Dieter

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

## Re: Creating a Percentage

100 * sales_amount / cumulative_sum?

Dieter

Enthusiast

yes Dieter