This topic describes the general syntax of InfluxQL functions.
Specify multiple functions in a SELECT statement
Syntax
SELECT <function>(),<function>() FROM_clause [...]
Description
Separate multiple functions in a SELECT
statement with commas (,
). The syntax applies to all the InfluxQL functions except TOP()
and BOTTOM()
functions. TOP()
and BOTTOM()
functions cannot be used in combination with other functions in a SELECT
statement.
Example 1: Calculate the average and median of the field values by using a query
> SELECT MEAN("water_level"),MEDIAN("water_level") FROM "h2o_feet"
name: h2o_feet
time mean median
--------------
1970-01-01T00:00:00Z4.4421070258225224.124
This query returns the average and median of the field values for the water_level
field key.
Example 2: Obtain the most frequent field values of two field keys by using a query
> SELECT MODE("water_level"),MODE("level description") FROM "h2o_feet"
name: h2o_feet
time mode mode_1
--------------
1970-01-01T00:00:00Z2.69 between 3 and 6 feet
This query returns the most frequent field value of the water_level
field key and the most frequent field value of the level description
field key. The result for the water_level
field key is displayed in the mode
column, and the result for the level description
field is displayed in the mode_1
column. The name of each output column must be unique in the query results. Therefore, the second column mode
is renamed mode_1
.
For more information about how to specify names for output columns, see Rename output field keys.
Example 3: Obtain the largest and the smallest field values by using a query
> SELECT MIN("water_level"), MAX("water_level")[...]
name: h2o_feet
time min max
----------
1970-01-01T00:00:00Z-0.619.964
This query returns the largest and the smallest field values of the water_level
field key.
Note: This query returns the 1970-01-01T00:00:00Z
timestamp. This timestamp is a null timestamp in TSDB for InfluxDB®. MIN()
and MAX()
are selector functions. If a SELECT
statement contains only one selector function, a specific timestamp is returned. The MIN()
and MAX()
functions return two different timestamps, as shown in the following example. Therefore, the system overwrites the two timestamps with a null timestamp.
> SELECT MIN("water_level") FROM "h2o_feet"
name: h2o_feet
time min
-------
2015-08-29T14:30:00Z-0.61<---Timestamp1
> SELECT MAX("water_level") FROM "h2o_feet"
name: h2o_feet
time max
-------
2015-08-29T07:24:00Z9.964<---Timestamp2
Rename output field keys
Syntax
SELECT <function>() AS <field_key>[...]
Description
By default, the query results are displayed in the related sections of the field keys that are specified in functions. You can use an AS
clause to specify the name of an output field key in your query.
Example 1: Specify an output field key
> SELECT MEAN("water_level") AS "dream_name" FROM "h2o_feet"
name: h2o_feet
time dream_name
--------------
1970-01-01T00:00:00Z4.442107025822522
This query returns the average field value of the water_level
field key and renames the output field key dream_name
. If no AS
clauses are included, this query returns mean
as the output field key.
> SELECT MEAN("water_level") FROM "h2o_feet"
name: h2o_feet
time mean
--------
1970-01-01T00:00:00Z4.442107025822522
Example 2: Specify output field keys for multiple functions
> SELECT MEDIAN("water_level") AS "med_wat",MODE("water_level") AS "mode_wat" FROM "h2o_feet"
name: h2o_feet
time med_wat mode_wat
-------------------
1970-01-01T00:00:00Z4.1242.69
This query returns the median of the field values for the water_level
field key and the most frequently occurring field value of the water_level
field key. This query renames the output field keys and returns med_wat
and mode_wat
as the output field keys. If no AS
clauses are included, this query returns median
and mode
as the output field keys.
> SELECT MEDIAN("water_level"),MODE("water_level") FROM "h2o_feet"
name: h2o_feet
time median mode
--------------
1970-01-01T00:00:00Z4.1242.69
Change the return values for time intervals in which no data is reported
By default, queries that contain InfluxQL functions and GROUP BY time()
clauses return null values. This occurs when no data is reported in the related time intervals. If you do not want to obtain null values, add the GROUP BY
function to the end of the fill()
clause in your query.