Aggregate (AVG, Min, MAX..) Functions - Limitations

To use the aggregate functions (AVG, Min, MAX ...etc) in NGA queries, specify a SORT BY when using GROUP BY for queries. Otherwise the query parser will produce invalid results.

GROUP BY

InfluxDB supports two GROUP BYs for aggregate queries:

  • EL
  • SECS

EL, which stands for element, groups the values by DPE.

This means that: when using AVG, separate averages are calculated for:
  1. Motor1.temperature,
  2. Motor2.temperature and
  3. Motor1.powerUsage
Note:

Grouping of DP, DPT, SYS or DT elements is not supported.

EL is supported for grouping of elements and implicitly and cannot be disabled.

SECS

SECS time-based grouping mechanism is supported. For example, GROUP BY SECS(300) will provide a 5-minute average over the queried timerange.

To retrieve a timestamp when a value was calculated, add _offline.._stime to the SELECT, without an aggregate function.

SELECT '_offline.._stime', 'AVG(_offline.._value)' FROM .... GROUP BY SECS(300)

Note: MONTH does not work with InfluxDB Backend.