Null-value handling in aggregate functions is different from null handling in simple equations, for example making sum(a+b) functionally different from sum(a)+sum(b). This may result in complex expressions involving aggregation which are incorrect for the target use-case.

Care should be taken to determine whether the simple-expression always returns the desired results prior to using it within the bounds of an aggregation function. When a null value is input into a non-aggregate equation, the result is null regardless of the other field values. When a null is present in the set of inputs into an aggregate function, the null is ignored.

For example, shown here is a LiveView table of double values indexed on field 'id':

lv-client "select * from datatable"
 +----+------+------+
 | id | a    | b    |
 +----+------+------+
 | 1  | null | 2.0  |
 | 2  | 4.0  | null |
 | 3  | 1.0  | 5.0  |
 +----+------+------+
Rows = 3

 

Here are the columns summed individually:

lv-client "select sum(a) as sum_a, sum(b) as sum_b from datatable"
 +-------+-------+
 | sum_a | sum_b |
 +-------+-------+
 | 5.0   | 7.0   |
 +-------+-------+
Rows = 1

 

Here the individual values are added (a simple equation) before aggregation:

lv-client "select sum(a+b) as sum_ab from datatable"
 +--------+
 | sum_ab |
 +--------+
 | 6.0    |
 +--------+
Rows = 1

This result shows that rows 1 and 2 each result in 'null' which is ignored and only the values from row 3 participate.

Here the columns are individually summed and then the result added:

lv-client "select sum(a)+sum(b) as ab_sum from datatable"
 +--------+
 | ab_sum |
 +--------+
 | 12.0   |
 +--------+
Rows = 1

This result shows that individually summed, all values from column a and b participate.

Live Datamart Aggregate Query Null-value Handling