Forum Discussion
odata pull - calculations not pulling correctly
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
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.
2 Replies
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.Thank you! This helps!
