All Products
Search
Document Center

Aggregate functions

Last Updated: Jul 29, 2021

This topic describes the syntax and parameters of aggregate functions. It also provides multiple examples.

COUNT()

Returns the number of non-null field values.

Syntax

SELECT COUNT([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Syntax for nested structures

SELECT COUNT(DISTINCT([*|<field_key>|/<regular_expression>/]))[...]

Description

COUNT(field_key): returns the number of non-null field values for the specified field key.

COUNT(/regular_expression/): returns the number of non-null field values for each field key that matches the specified regular expression.

COUNT(*): returns the number of non-null field values for each field key in the specified measurement.

The COUNT() function supports field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

Example 1: Calculate the number of non-null field values for the specified field key

SELECT COUNT("water_level") FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z15258

This query returns the number of non-null field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the number of non-null field values for each field key in the specified measurement

SELECT COUNT(*) FROM "h2o_feet"

name: h2o_feet
time                   count_level description   count_water_level
--------------------------------------------
1970-01-01T00:00:00Z1525815258

This query returns the number of non-null field values for each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

Example 3: Calculate the number of non-null field values for each field key that matches the specified regular expression

SELECT COUNT(/water/) FROM "h2o_feet"

name: h2o_feet
time                   count_water_level
---------------------
1970-01-01T00:00:00Z15258

This query returns the number of non-null field values for each field key that contains water in the h2o_feet measurement.

Example 4: Calculate the number of non-null field values for the specified field key in each time interval by using a query that includes multiple clauses

SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(200) LIMIT 7 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   count
---------
2015-08-17T23:48:00Z200
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:36:00Z2
2015-08-18T00:48:00Z2

This query returns the number of non-null field values for the water_level field key in each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 200. This query limits the number of returned points to 7, and limits the number of returned series to 1.

Example 5: Calculate the number of unique field values for the specified field key

SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z4

This query returns the number of unique field values for the level description field key in the level description measurement.

FAQ

For time intervals in which no data is reported, most InfluxQL functions return null values. If you do not want to obtain null values, use the fill() function. The fill(<fill_option>) function replaces each null value with the value of the fill_option parameter. If no data is reported during some time intervals, the COUNT() function returns 0. If you use the fill(<fill_option>) function in the query, the function replaces 0 with the value of the fill_option parameter.

Examples

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-09-18T21:24:00Z' AND time <='2015-09-18T21:54:00Z' GROUP BY time(12m)

name: h2o_feet
time                   count
---------
2015-09-18T21:24:00Z2
2015-09-18T21:36:00Z2
2015-09-18T21:48:00Z0

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-09-18T21:24:00Z' AND time <='2015-09-18T21:54:00Z' GROUP BY time(12m) fill(800000)

name: h2o_feet
time                   count
---------
2015-09-18T21:24:00Z2
2015-09-18T21:36:00Z2
2015-09-18T21:48:00Z800000

The first query in the preceding code block does not use the fill() function. In the query result, 0 is returned for the last time interval. This is because no data is reported during the last time interval. The second query uses the fill(800000) function. In this query, no data is reported during the last interval. For the last time interval, the fill(800000) function returns 800000. If you do not use the fill() function, 0 is returned for the last time interval.

DISTINCT()

Returns the unique field values of the specified field keys.

Syntax

SELECT DISTINCT([*|<field_key>|/<regular_expression>/]) FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Syntax for nested structures

SELECT COUNT(DISTINCT([*|<field_key>|/<regular_expression>/]))[...]

Description

DISTINCT(field_key): returns the unique field values of specified field key.

DISTINCT(/regular_expression/): returns the unique field values of each field key that matches the specified regular expression.

DISTINCT(*): returns the unique field values of each field key in the specified measurement.

The DISTINCT() function supports field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

Example 1: List the unique field values of the specified field key

SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
------------
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

This query returns the unique field values of the level description field key in the h2o_feet measurement.

Example 2: List the unique field values of each field key in the specified measurement

SELECT DISTINCT(*) FROM "h2o_feet"

name: h2o_feet
time                   distinct_level description   distinct_water_level
--------------------------------------------------
1970-01-01T00:00:00Z   between 6 and 9 feet         8.12
1970-01-01T00:00:00Z   between 3 and 6 feet         8.005
1970-01-01T00:00:00Z   at or greater than 9 feet    7.887
1970-01-01T00:00:00Z   below 3 feet                 7.762
[...]

This query returns the unique field values of each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

Example 3: List the unique field values of each field key that matches the specified regular expression

SELECT DISTINCT(/description/) FROM "h2o_feet"

name: h2o_feet
time                   distinct_level description
------------------------------
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

This query returns the unique field values of each field key that contains description in the h2o_feet measurement.

Example 4: List the unique field values of the specified field key by using a query that includes multiple clauses

>  SELECT DISTINCT("level description") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   distinct
------------
2015-08-18T00:00:00Z   between 6 and 9 feet
2015-08-18T00:12:00Z   between 6 and 9 feet
2015-08-18T00:24:00Z   between 6 and 9 feet
2015-08-18T00:36:00Z   between 6 and 9 feet
2015-08-18T00:48:00Z   between 6 and 9 feet

This query returns the unique field values of the level description field key. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. This query limits the number of returned series to 1.

Example 5: Calculate the number of unique field values for the specified field key

SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z4

This query returns the unique field values of the level description field key in the h2o_feet measurement.

FAQ

If you use the DISTINCT() function in an DISTINCT() clause, TSDB for InfluxDB® may overwrite some points in the target measurement. In most cases, the DISTINCT() function returns multiple field values that have the same timestamp. TSDB for InfluxDB® considers the points that have the same timestamp in the same series as duplicate points, and uses the latest points to overwrite the duplicate points in the target measurement.

Examples

The first query in the following code block uses the DISTINCT() function and returns four field values. Note: The four field values have the same timestamp. The second query uses an INTO clause and writes the returned field values to the distincts measurement. The last query returns all the data in the distincts measurement. The four initial points are considered as duplicate points because these points belong to the same series and have the same timestamp. Therefore, the last query returns only one point. If duplicate points exist, TSDB for InfluxDB® uses the latest point to overwrite the previous points.

SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
------------
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

SELECT DISTINCT("level description") INTO "distincts" FROM "h2o_feet"

name: result
time                   written
-----------
1970-01-01T00:00:00Z4

SELECT * FROM "distincts"

name: distincts
time                   distinct
------------
1970-01-01T00:00:00Z   at or greater than 9 feet

INTEGRAL()

Returns the area of the section that resides between the field value curve and the x-axis. The area indicates the integral of the field values.

Syntax

SELECT INTEGRAL([*|<field_key>|/<regular_expression>/][,<unit>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

TSDB for InfluxDB® calculates the areas of the sections that reside between the field value curves and the x-axis. Then, TSDB for InfluxDB® converts the results into the total areas based on the time granularity that is specified by the unit parameter. The value of the unit parameter is an integer that is suffixed by a time unit. This parameter is optional. If you do not specify the unit parameter in your query, the default value 1s is specified for the unit parameter.

INTEGRAL(field_key): returns the area of the section that resides between the field value curve and the x-axis for each field key in the specified measurement.

INTEGRAL(/regular_expression/): returns the area of the section that resides between the field value curve and the x-axis for each field key that matches the specified regular expression.

INTEGRAL(*): returns the area of the section that resides between the field value curve and the x-axis for each field key in the specified measurement.

The INTEGRAL() function cannot be nested in the fill() function. The INTEGRAL() function supports INT64 and FLOAT64 field values.

Example 1: Calculate the integral of the field values for the specified field key

SELECT INTEGRAL("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral
------------
1970-01-01T00:00:00Z3732.66

This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-second interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the integral of the field values for the specified field key and specify the unit parameter

SELECT INTEGRAL("water_level",1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral
------------
1970-01-01T00:00:00Z62.211

This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-second interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

Example 3: Calculate the integral of the field values for each field key in the specified measurement and specify the unit parameter

SELECT INTEGRAL(*,1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral_water_level
------------------------
1970-01-01T00:00:00Z62.211

This query returns the area of the section that resides between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

Example 4: Calculate the integral of the field values for each field key that matches the specified regular expression and specify the unit parameter

SELECT INTEGRAL(/water/,1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral_water_level
------------------------
1970-01-01T00:00:00Z62.211

This query returns the area of the section that resides between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key contains water and stores numeric values in the h2o_feet measurement.

Example 5: Calculate the integral of the field values for the specified field key by using a query that includes multiple clauses

SELECT INTEGRAL("water_level",1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m) LIMIT 1

name: h2o_feet
time                 integral
------------
2015-08-18T00:00:00Z24.972

This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-minute interval for the water_level field key in the h2o_feet measurement. The specified time range is from 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval. The query limits the number of returned points to 1.

MEAN()

Returns the average field values of the specified field keys.

Syntax

SELECT MEAN([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

MEAN(field_key): returns the average field value of a specified field key.

MEAN(/regular_expression/): returns the average field value of each field key that matches the specified regular expression.

MEAN(*): returns the average field value of each field key in the specified measurement.

The MEAN() function supports INT64 and FLOAT64 field values.

Example 1: Calculate the average field value of the specified field key

SELECT MEAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   mean
--------
1970-01-01T00:00:00Z4.442107025822522

This query returns the average field value of the water_level field key in the h2o_feet measurement.

Example 2: Calculate the average field value of each field key in the specified measurement

SELECT MEAN(*) FROM "h2o_feet"

name: h2o_feet
time                   mean_water_level
--------------------
1970-01-01T00:00:00Z4.442107025822522

This query returns the average field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

Example 3: Calculate the average field value of each field key that matches the specified regular expression

SELECT MEAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mean_water_level
--------------------
1970-01-01T00:00:00Z4.442107025822523

This query returns the average field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the average field value of the specified field key for each time interval by using a query that includes multiple clauses

SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 7 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   mean
--------
2015-08-17T23:48:00Z9.01
2015-08-18T00:00:00Z8.0625
2015-08-18T00:12:00Z7.8245
2015-08-18T00:24:00Z7.5675
2015-08-18T00:36:00Z7.303
2015-08-18T00:48:00Z7.046

This query returns the average field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01 This query limits the number of returned points to 7, and limits the number of returned series to 1.

MEDIAN()

Returns the median in a set of sorted field values.

Syntax

SELECT MEDIAN([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

MEDIAN(field_key): returns the median of the field values for the specified field key.

MEDIAN(/regular_expression/): returns the median of the field values for each field key that matches the specified regular expression.

MEDIAN(*): returns the median of the field values for each field key in the specified measurement.

The MEDIAN() function supports INT64 and FLOAT64 field values.

Note

The MEDIAN() function is similar to the PERCENTILE(field_key, 50) function unless the specified field key has an even number of field values. In this case, the MEDIAN() function returns the average of the two middle field values.

Example 1: Calculate the median of the field values for the specified field key

SELECT MEDIAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   median
----------
1970-01-01T00:00:00Z4.124

This query returns the median of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the median of the field values for each field key in the specified measurement

SELECT MEDIAN(*) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----------------------
1970-01-01T00:00:00Z4.124

This query returns the median of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

Example 3: Calculate the median of the field values for each field key that matches the specified regular expression

SELECT MEDIAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----------------------
1970-01-01T00:00:00Z4.124

This query returns the median of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the median of the field values for the specified field key in each time interval by using a query that includes multiple clauses

SELECT MEDIAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(700) LIMIT 7 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   median
----------
2015-08-17T23:48:00Z700
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
2015-08-18T00:36:00Z2.0620000000000003
2015-08-18T00:48:00Z700

This query returns the median of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 700. This query limits the number of returned points to 7, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

MODE()

Returns the most frequently occurring fields values.

Syntax

SELECT MODE([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

MODE(field_key): returns the most frequent frequently occurring field values of the specified field key.

MODE(/regular_expression/): returns the most frequently occurring field value of each field key that matches the specified regular expression.

MODE(*): returns the most frequently occurring field value of each field key in the specified measurement.

The MODE() function supports field values of all data types.

Note

If two or more field values have the maximum number of occurrences and they are interrelated, the MODE() function returns the field value that is attached with the earliest timestamp.

Example 1: Obtain the most frequently occurring field value of the specified field key

SELECT MODE("level description") FROM "h2o_feet"

name: h2o_feet
time                   mode
--------
1970-01-01T00:00:00Z   between 3 and 6 feet

This query returns the most frequently occurring field value of the level description field key in the h2o_feet measurement.

Example 2: Obtain the most frequently occurring field value of each field key in the specified measurement

SELECT MODE(*) FROM "h2o_feet"

name: h2o_feet
time                   mode_level description   mode_water_level
------------------------------------------
1970-01-01T00:00:00Z   between 3 and 6 feet     2.69

This query returns the most frequent field value of each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

Example 3: Obtain the most frequently occurring field value of each field key that matches the specified regular expression

SELECT MODE(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mode_water_level
--------------------
1970-01-01T00:00:00Z2.69

This query returns the most frequent field value of each field key that contains water in the h2o_feet measurement.

Example 4: Obtain the most frequently occurring field value of the specified field key for each time interval by using a query that includes multiple clauses

SELECT MODE("level description") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* LIMIT 3 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   mode
--------
2015-08-17T23:48:00Z
2015-08-18T00:00:00Z   below 3 feet
2015-08-18T00:12:00Z   below 3 feet

This query returns the most frequent field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

SPREAD()

Returns the difference between the maximum and the minimum field values of the specified field keys.

Syntax

SELECT SPREAD([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

SPREAD(field_key): returns the difference between the maximum and the minimum field values of the specified field key.

SPREAD(/regular_expression/): returns the difference between the maximum and the minimum field values of each field key that matches the specified regular expression.

SPREAD(*): returns the difference between the maximum and the minimum field values of each field key in the specified measurement.

The SPREAD() function supports INT64 and FLOAT64 field values.

Example 1: Calculate the difference between the maximum and the minimum field values of the specified field key

SELECT SPREAD("water_level") FROM "h2o_feet"

name: h2o_feet
time                   spread
----------
1970-01-01T00:00:00Z10.574

This query returns the difference between the maximum and the minimum field values of the water_level field key in the h2o_feet measurement.

Example 2: Calculate the difference between the maximum and the minimum field values of each field key in the specified measurement

SELECT SPREAD(*) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----------------------
1970-01-01T00:00:00Z10.574

This query returns the difference between the maximum and the minimum field values of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

Example 3: Calculate the difference between the maximum and the minimum field values of each field key that matches the specified regular expression

SELECT SPREAD(/water/) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----------------------
1970-01-01T00:00:00Z10.574

This query returns the difference between the maximum and the minimum field values of each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the difference between the maximum and the minimum field values of the specified field key for each time interval by using a query that includes multiple clauses

SELECT SPREAD("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18) LIMIT 3 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   spread
----------
2015-08-17T23:48:00Z18
2015-08-18T00:00:00Z0.052000000000000046
2015-08-18T00:12:00Z0.09799999999999986

This query returns the difference between the maximum and the minimum field values of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

STDDEV()

Returns the standard deviation of the specified field values.

Syntax

SELECT STDDEV([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

STDDEV(field_key): returns the standard deviation of the field values for the specified field key.

STDDEV(/regular_expression/): returns the standard deviation of the field values for each field key that matches the specified regular expression.

STDDEV(*): returns the standard deviation of the field values for each field key in the specified measurement.

The STDDEV() function supports INT64 or FLOAT64 field values.

Example 1: Calculate the standard deviation of the field values for the specified field key

SELECT STDDEV("water_level") FROM "h2o_feet"

name: h2o_feet
time                   stddev
----------
1970-01-01T00:00:00Z2.279144584196141

This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the standard deviation of the field values for each field key in the specified measurement

SELECT STDDEV(*) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----------------------
1970-01-01T00:00:00Z2.279144584196141

This query returns the standard deviation of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

Example 3: Calculate the standard deviation of the field values for each field key that matches the specified regular expression

SELECT STDDEV(/water/) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----------------------
1970-01-01T00:00:00Z2.279144584196141

This query returns the standard deviation of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the standard deviation of the field values for the specified field key in each time interval by using a query that includes multiple clauses

SELECT STDDEV("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 2 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   stddev
----------
2015-08-17T23:48:00Z18000
2015-08-18T00:00:00Z0.03676955262170051

This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 2, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

SUM()

Returns the sum of the specified field values.

Syntax

SELECT SUM([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

Description

SUM(field_key): returns the sum of the field values for the specified field key.

SUM(/regular_expression/): returns the sum of the field values for each field key that matches the specified regular expression.

SUM(*): returns the sum of the field values for each field key in the specified measurement.

The SUM() function supports INT64 and FLOAT64 field values.

Example 1: Calculate the sum of the field values for the specified field key

SELECT SUM("water_level") FROM "h2o_feet"

name: h2o_feet
time                   sum
-------
1970-01-01T00:00:00Z67777.66900000004

This query returns the sum of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the sum of the field values for each field key in the specified measurement

SELECT SUM(*) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
-------------------
1970-01-01T00:00:00Z67777.66900000004

This query returns the sum of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

Example 3: Calculate the sum of the field values for each field key that matches the specified regular expression

SELECT SUM(/water/) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
-------------------
1970-01-01T00:00:00Z67777.66900000004

This query returns the sum of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the sum of the field values for the specified field key in each time interval by using a query that includes multiple clauses

SELECT SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 4 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   sum
-------
2015-08-17T23:48:00Z18000
2015-08-18T00:00:00Z16.125
2015-08-18T00:12:00Z15.649
2015-08-18T00:24:00Z15.135

This query returns the sum of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 4, and limits the number of returned series to 1.