Use an aggregate function

The following query returns the minimum, maximum, average, and sum of the tag 'ReactLevel' from the WideHistory table.

SELECT * FROM OpenQuery(INSQL,'

SELECT "Minimum" = min(ReactLevel),

"Maximum" = max(ReactLevel),

"Average" = avg(ReactLevel),

"Sum" = sum(ReactLevel)

FROM WideHistory

WHERE DateTime > "2001-02-28 18:55:00 "

AND DateTime < "2001-02-28 19:00:00"

AND wwRetrievalMode = "Cyclic"

')

The results are:

(1 row(s) affected)

If you perform a SUM or AVG in delta retrieval mode against the Wide table, the aggregation will only be performed when the value has changed. The aggregation will not apply to all of the rows returned for each column.

For example, the following query has no aggregation applied:

SELECT * FROM OpenQuery(INSQL, 'SELECT DateTime, SysTimeHour, SysTimeMin, SysTimeSec, SysDateDay

FROM AnalogWideHistory

WHERE DateTime >= "2001-08-15 13:20:57.345"

AND DateTime < "2001-08-15 13:21:03.345"

AND wwRetrievalMode = "Delta"

')

GO

The results are:

Then, a SUM is applied to all of the returned column values:

SELECT * FROM OpenQuery(INSQL,'SELECT Sum(SysTimeHour), Sum(SysTimeMin), Sum(SysTimeSec), Sum(SysDateDay)

FROM WideHistory

WHERE DateTime >= "2001-08-15 13:20:57.345"

AND DateTime < "2001-08-15 13:21:03.345"

AND wwRetrievalMode = "Delta"

')

GO

The results are:

Thus, for delta retrieval mode, a SUM or AVG is applied only if the value has changed from the previous row.

If you perform an AVG in delta retrieval mode, AVG will be computed as:

SUM of delta values/number of delta values

For example, an AVG is applied to all of the returned column values:

SELECT * FROM OpenQuery(INSQL,'SELECT Avg(SysTimeHour), Avg(SysTimeMin), Avg(SysTimeSec), Avg(SysDateDay)

FROM WideHistory

WHERE DateTime >= "2001-08-15 13:20:57.345"

AND DateTime < "2001-08-15 13:21:03.345"

AND wwRetrievalMode = "Delta"

')

GO

The results are: