SQL Aggregate Functions
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.
| 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 ihTagsExample 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.
