odata pull - calculations not pulling correctly

[ak105]

Hello all, 

I have been pulling data via odata and been trying to calculate percentages within my query

The code is as follows:

 &"aggregate(" 
&"$count as TotalCount," 
&"cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as PassedCount," 
&"cast(LastResultOutcome eq 'Failed', Edm.Int32) with sum as FailedCount," 
&"cast(LastResultOutcome eq 'None', Edm.Int32) with sum as NotRunCount," 
&"cast(LastResultOutcome ne 'None', Edm.Int32) with sum as RunCount" 
&")" 
&")" 
&"/compute(" 
&"RunCount div TotalCount as RunPercentage," 
&"NotRunCount div TotalCount as NotRunPercentage," 
&"iif(TotalCount gt NotRunCount, PassedCount div RunCount,0) as PassedPercentage," 
&"iif(TotalCount gt NotRunCount, FailedCount div RunCount,0) as FailedPercentage" 
&")" 
&"&$orderby=RunPercentage desc", null, [Implementation="2.0"])

 however, these are my results:

 TotalCount PassedCount FailedCount NotRunCount RunCount RunPercentage NotRunPercentage PassedPercentage FailedPercentage
 63         21          0           42          21       0             0                1                0  

was wondering why the percentage fields are returning either 0 or 1. Hope someone can help me out here

[ak105]

Thank you! This helps! 

[v-jingzhang · Accepted solution]

Hi @ak105 

The results are correct because both dividend and divisor are Integers. When you use div or /, it returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL). If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated. Reference: / (Division) (Transact-SQL). 

To have the percentage fields return decimal numbers, you can convert the dividends or divisors to float or decimal data types. 

Data type conversion (Database Engine) 

CAST and CONVERT (Transact-SQL) 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.