In some cases, Expressions with a division will give an unexpected result : 0 everywhere, or imprecise value for example.
Example Expression :
Expression1 = 100 * (SUM([isActive]) / COUNT([ID]))
In this case I want the % of Active items.
[isActive] is 0 or 1 and ID is some ID for the items.
Why am I getting weird results for this Expression1 ?
(0 everywhere, or imprecise value for example)
Expressions are executed as part of the final SQL query.
For buffer-based Connections (Excel, CSV, Historian, Web, …), the final queries are also database queries.
SQL division behaves differently if the two sides are Integer.
Test the following SQL query :
The result is 0
We are performing an Integer Division (see https://en.wikipedia.org/wiki/Euclidean_division)
The quotient is 0 and the remainder is 123.
To explore further, you can test the following SQL query (paste in SQL Management Studio for example) :
SELECT 29/10 AS quotient, -- 2 29%10 AS remainder, -- 9 (29/10) * 10 + 29%10 AS result, -- 29 = 2*10 + 9 1.0 * (29/10) AS wrong, -- 2.0 (1.0 * 29)/10 AS real_division; -- 2.9 because 1.0 * 29 = 29.0
In my example Expression1 above, SUM, and the result of COUNT is also an Integer.
So I am not going to get the % of Active items I expect
To fix it, I need to force the conversion to a Real number for at least one side of the division.
Example solution :
Expression2 = 100 * (SUM([isActive] * 1.0) /COUNT([ID]))