Pitfall : Incorrect result in Expression with division

Your issue

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)

Explanation

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 :

SELECT 123/456;

The result is 0

Why ?

We are performing an Integer Division (see https://en.wikipedia.org/wiki/Euclidean_division)

The quotient is 0 and the remainder is 123.

More info

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

Solution

To fix it, I need to force the conversion to a Real number for at least one side of the division.

Example solution :

*Multiply one member by 1.0 to force the conversion to a Real number.

Expression2 = 100 * (SUM([isActive] * 1.0) /COUNT([ID]))

Code192 2020 All rights reserved