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.
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!)
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