Creating a Percentage

Database
Teradata Employee

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
9 REPLIES
Junior Contributor

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
Teradata Employee

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,
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
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

Junior Contributor

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
Thanks for your inputs.

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

Re: Creating a Percentage

100 * sales_amount / cumulative_sum?

Dieter

Enthusiast

Re: Creating a Percentage

yes Dieter