Division results in a 0.0 value... why?!

Database
Fan

Division results in a 0.0 value... why?!

Hi

In my query I have a couple of sub queries to get some values and everything is ok except the calulation of a percentage which always is zero.  I'm really struggling with it, despite trying CAST and other things to get the answers.

SELECT
a.NBR,
a.ACTUAL as ACT,
o.OPPORTUNITY as OPP
(a.ACTUAL+o.OPPORTUNITY) as TOTAL,
Case when TOTAL = 0 then null else ((a.ACTUAL / TOTAL) * 100.0) End as PERCNT

An example return from the above is this:

110, 163, 273, 0.0

So, 110 + 163 = 273.  All good.

Why does ( 110 / 273 ) * 100 = 0.0 ?

I want it to say ( 110 / 273 ) * 100 = 40.3

Even i fI strip out the CASE statement it does not work.  (CASE is there to trap any Div By Zero issues)

Any help appreciated (including any beginner chastisement for any stupid errors!)

Tags (1)
2 REPLIES
Junior Contributor

Re: Division results in a 0.0 value... why?!

After each clavulation the intermediate result is rounded (in your case it's probably an integer, so it's truncated) with no additional intermediate precision (at least this is the rule in Teradata, other DBMSes have different rules):

110/273 -> 0

0 * 100.00 = 0,00

You have to multiply first and then divide, this is a rule of thumb in Teradata:

100.0 * a.ACTUAL / TOTAL

Dieter

Fan

Re: Division results in a 0.0 value... why?!

Dieter, thank you so much for this.  It works.

Respect!

Cheers

Hanco