SQL Aggregate Functions | Historian 7.2 Documentation

SQL aggregate functions perform a calculation on a set of values in a column and return a single value. For instance, when comparing multiple tags, you could retrieve the minimum (MIN) of the returned minimum values. You usually use aggregate functions with the GROUP BY clause, but it is not required. For more information, see GROUP BY.

The Historian OLE DB Provider supports the aggregate functions described in the following table.

Table 1. Supported Aggregate Functions
Function	Description
`AVG`	Returns the average of the values in a group. Null values are ignored.
`COUNT`	Returns the number of items in a group. Null values are not ignored.
`MAX`	Returns the maximum value in a group. Null values are ignored.
`MIN`	Returns the minimum value in a group. Null values are ignored.
`SUM`	Returns the sum of all the values in a group. `SUM` can be used with numeric columns only. Null values are ignored.
`STDEV`	Returns the statistical standard deviation of all values in a group. Null values are ignored.
`STDEVP`	Returns the statistical standard deviation for the population for all values in a group. Null values are ignored.
`VAR`	Returns the statistical variance of all values in a group. Null values are ignored.
`VARP`	Returns the statistical variance for the population for all values in a group. Null values are ignored.

STDEV, STDEVP, VAR, and VARP

If a variance is defined as the deviation from an average data set value, and N is the number of values in the data set, then the following equations apply:

VAR = (Sum of Variances)^2 / (N - 1)
VARP = (Sum of Variances)^2 / (N)
STDEV = SquareRoot (VAR)
STDEVP = SquareRoot (VARP)

SQL Aggregate Functions and the CalculationMode Column

For information on the differences between SQL aggregate functions and the CalculationMode columns, refer to How Are Historian Calculation Modes and SQL Aggregate Functions Different?.

Example 1: Retrieve the Total Number of Tags

The following example displays the use of the aggregate COUNT() function without GROUP BY.

SELECT COUNT(*) FROM ihTags

Example 2: Calculate Values for Multiple Tags

The following example displays the use of aggregate functions on grouped rows.

FROM ihrawdata WHERE tagname LIKE '*0001*'
AND timestamp>='28-dec-2001 00:00'
AND timestamp<='29-dec-2001 00:00'
AND samplingmode=interpolated
AND intervalmilliseconds=1h GROUP BY tagname ORDER BY tagname

The following figure displays the results of the previous query. Note the column names (Sum of value, Avg of value, Min of value, and Max of value) returned for the calculated columns.

Figure: Query Results in the Historian Interactive SQL Application