Aggregate data with InfluxQL
An InfluxQL query that aggregates data includes the following clauses:
* Required- *
SELECT
: Specify fields and calculations to output from a measurement or use the wildcard alias (*
) to select all fields and tags from a measurement. - *
FROM
: Specify the measurement to query data from. WHERE
: Only retrieve data that meets the specified conditions–for example, falls within a time range, contains specific tag values, or contains a field value outside a specified range.GROUP BY
: Group data by tag values and time intervals.
For simplicity, the term “aggregate” in this guide refers to applying both aggregate and selector functions to a dataset.
Learn how to apply aggregate operations to your queried data:
Aggregate and selector functions
Both aggregate and selector functions return a limited number of rows from each group.
Aggregate functions return a single row, whereas some selector functions let you
specify the number of rows to return from each group.
For example, if you GROUP BY room
and perform an aggregate operation
in your SELECT
clause, results include an aggregate value for each unique
value of room
.
Aggregate functions
Use aggregate functions to aggregate values in a specified field for each group and return a single row per group containing the aggregate field value.
Basic aggregate query
SELECT MEAN(co) from home
Selector functions
Use selector functions to “select” a value from a specified field.
Basic selector query
SELECT TOP(co, 3) from home
Example aggregate queries
Sample data
The following examples use the sample data written in the Get started home sensor data. To run the example queries and return results, write the sample data to your InfluxDB Cloud Dedicated database before running the example queries.
Perform an ungrouped aggregation
To aggregate all queried values in a specified field:
- Use aggregate or selector functions in your
SELECT
statement. - Do not include a
GROUP BY
clause to leave your data ungrouped.
SELECT MEAN(co) AS "average co" FROM home
Group and aggregate data
To apply aggregate or selector functions to grouped data:
- Use aggregate or selector functions in your
SELECT
statement. - Include a
GROUP BY
clause with a comma-delimited list of tags to group by.
Keep the following in mind when using GROUP BY
:
GROUP BY
can use column aliases that are defined in theSELECT
clause.
SELECT
MEAN(temp) AS "average temp"
FROM home
GROUP BY room
Downsample data by applying interval-based aggregates
A common use case when querying time series is downsampling data by applying aggregates to time-based groups. To group and aggregate data into time-based groups:
In your
SELECT
clause, apply aggregate or selector functions to queried fields.In your
WHERE
clause, include time bounds for the query. Interval-based aggregates produce a row for each specified time interval. If no time bounds are specified in theWHERE
clause, the query uses the default time range (1970-01-01T00:00:00Z to now) and returns a row for each interval in that time range.In your
GROUP BY
clause:- Use the
time()
function to specify the time interval to group by. - Optional: Specify other tags to group by.
- Use the
The following example retrieves unique combinations of time intervals and rooms with their minimum, maximum, and average temperatures.
SELECT
MAX(temp) AS "max temp",
MIN(temp) AS "min temp",
MEAN(temp) AS "average temp"
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time < '2022-01-01T20:00:00Z'
GROUP BY time(2h), room
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.