Skip to main content

GROUP BY Clause

About 5 min

GROUP BY Clause

1. Syntax Overview

GROUP BY expression (',' expression)*
  • The GROUP BY clause is used to group the result set of a SELECT statement based on the specified column values. The values of the grouping columns remain unchanged in the results, while other columns with the same grouping column values are calculated using specified aggregate functions (e.g., COUNT, AVG).

2. Notes

2.1 Items in the SELECT Clause

Items in the SELECT clause must either include aggregate functions or consist of columns specified in the GROUP BY clause.

Valid Example:

SELECT concat(device_id, model_id), avg(temperature) 
  FROM table1 
  GROUP BY device_id, model_id;

Result:

+-----+-----+
|_col0|_col1|
+-----+-----+
| 100A| 90.0|
| 100C| 86.0|
| 100E| 90.0|
| 101B| 85.0|
| 101D| 85.0|
| 101F| 90.0|
+-----+-----+
Total line number = 6
It costs 0.094s

Invalid Example 1:

SELECT device_id, temperature  
  FROM table1  
  GROUP BY device_id;

Error Message:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701:
  'temperature' must be an aggregate expression or appear in GROUP BY clause

Invalid Example 2:

SELECT device_id, avg(temperature) 
  FROM table1  
  GROUP BY model;

Error Message:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701:
  Column 'model' cannot be resolved

2.2 Without a GROUP BY Clause

If there is no GROUP BY clause, all items in the SELECT clause must either include aggregate functions or exclude them entirely.

Valid Example:

SELECT COUNT(*), avg(temperature) 
  FROM table1;

Result:

+-----+-----------------+
|_col0|            _col1|
+-----+-----------------+
|   18|87.33333333333333|
+-----+-----------------+
Total line number = 1
It costs 0.094s

Invalid Example:

SELECT humidity, avg(temperature) FROM table1;

Result:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: 
  'humidity' must be an aggregate expression or appear in GROUP BY clause

2.3 Using Constant Integers in GROUP BY Clause

The GROUP BY clause supports referencing SELECT items using constant integers starting from 1. If the constant is less than 1 or exceeds the size of the SELECT item list, an error will occur.

Example:

SELECT date_bin(1h, time), device_id, avg(temperature)
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-29 00:00:00
  GROUP BY 1, device_id;

Result:

+-----------------------------+---------+-----+
|                        _col0|device_id|_col2|
+-----------------------------+---------+-----+
|2024-11-28T08:00:00.000+08:00|      100| 85.0|
|2024-11-28T09:00:00.000+08:00|      100| null|
|2024-11-28T10:00:00.000+08:00|      100| 85.0|
|2024-11-28T11:00:00.000+08:00|      100| 88.0|
|2024-11-27T16:00:00.000+08:00|      101| 85.0|
+-----------------------------+---------+-----+
Total line number = 5
It costs 0.092s

2.4 Alias Restrictions in GROUP BY Clause

Aliases from SELECT items cannot be used in the GROUP BY clause. Use the original expression instead.

Example:

SELECT date_bin(1h, time) AS hour_time, device_id, avg(temperature)
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-29 00:00:00
  GROUP BY date_bin(1h, time), device_id;

Result:

+-----------------------------+---------+-----+
|                    hour_time|device_id|_col2|
+-----------------------------+---------+-----+
|2024-11-28T08:00:00.000+08:00|      100| 85.0|
|2024-11-28T09:00:00.000+08:00|      100| null|
|2024-11-28T10:00:00.000+08:00|      100| 85.0|
|2024-11-28T11:00:00.000+08:00|      100| 88.0|
|2024-11-27T16:00:00.000+08:00|      101| 85.0|
+-----------------------------+---------+-----+
Total line number = 5
It costs 0.092s

2.5 Referencing SELECT Aliases in GROUP BY Clause

  • Since V2.0.11.1, the GROUP BY clause supports referencing aliases explicitly defined in the SELECT clause.
    • This capability only applies to scenarios where the alias itself is directly referenced; names with table name prefixes (e.g., GROUP BY table1.hour_time) are not expanded as SELECT aliases and are still parsed as regular expressions.
    • Names referenced in GROUP BY are first resolved as input columns of the current query; only when they cannot be resolved as input columns will they be further resolved as SELECT aliases. If multiple aliases with the same name exist in the SELECT list, an ambiguity error will be thrown when referencing that alias.
    • After GROUP BY alias resolution, existing GROUP BY validation rules still apply. For example, grouping keys cannot contain aggregate functions, window functions, or grouping functions.

Valid Example:

SELECT date_bin(1h, time) AS hour_time, device_id, avg(temperature)
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-29 00:00:00
  GROUP BY hour_time, device_id;

Result:

+-----------------------------+---------+-----+
|                    hour_time|device_id|_col2|
+-----------------------------+---------+-----+
|2024-11-28T08:00:00.000+08:00|      100| 85.0|
|2024-11-28T09:00:00.000+08:00|      100| null|
|2024-11-28T10:00:00.000+08:00|      100| 85.0|
|2024-11-28T11:00:00.000+08:00|      100| 88.0|
|2024-11-27T16:00:00.000+08:00|      101| 85.0|
+-----------------------------+---------+-----+
Total line number = 5
It costs 0.228s

Invalid Example 1: Same statement contains aliases with the same name

SELECT temperature AS value, humidity AS value
  FROM table1
  GROUP BY value;

Result:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Column alias 'value' is ambiguous at positions 1, 2

Invalid Example 2: Grouping key contains an aggregate function

SELECT AVG(temperature) AS avg_temperature
  FROM table1
  GROUP BY avg_temperature;

Result:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: GROUP BY clause cannot contain aggregations, window functions or grouping operations: [AVG(temperature)]

2.6 Using Aggregate Functions with \*

Only the COUNT function can be used with * to calculate the total number of rows. Using * with other aggregate functions will result in an error.

Example:

SELECT count(*) FROM table1;

Result:

+-----+
|_col0|
+-----+
|   18|
+-----+
Total line number = 1
It costs 0.047s

3. Sample Data and Usage Examples

The Example Data pagepage provides SQL statements to construct table schemas and insert data. By downloading and executing these statements in the IoTDB CLI, you can import the data into IoTDB. This data can be used to test and run the example SQL queries included in this documentation, allowing you to reproduce the described results.

Example 1: Downsampling Time-Series Data

Downsample the temperature of device 101 over the specified time range, returning one average temperature per hour:

SELECT date_bin(1h, time) AS hour_time, AVG(temperature) AS avg_temperature
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-30 00:00:00
  AND device_id='101'
  GROUP BY 1;

Since V2.0.11.1, GROUP BY items can directly reference aliases explicitly defined in the SELECT clause, so the above SQL can be written as:

SELECT date_bin(1h, time) AS hour_time, AVG(temperature) AS avg_temperature
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-30 00:00:00
  AND device_id='101'
  GROUP BY hour_time;

Result:

+-----------------------------+---------------+
|                    hour_time|avg_temperature|
+-----------------------------+---------------+
|2024-11-29T10:00:00.000+08:00|           85.0|
|2024-11-27T16:00:00.000+08:00|           85.0|
+-----------------------------+---------------+
Total line number = 2
It costs 0.054s

Downsample the temperature of all devices over the past day, returning one average temperature per hour for each device:

SELECT date_bin(1h, time) AS hour_time, device_id, AVG(temperature) AS avg_temperature
  FROM table1
  WHERE time >= 2024-11-27 00:00:00  and time <= 2024-11-30 00:00:00
  GROUP BY 1, device_id;

Result:

+-----------------------------+---------+---------------+
|                    hour_time|device_id|avg_temperature|
+-----------------------------+---------+---------------+
|2024-11-29T11:00:00.000+08:00|      100|           null|
|2024-11-29T18:00:00.000+08:00|      100|           90.0|
|2024-11-28T08:00:00.000+08:00|      100|           85.0|
|2024-11-28T09:00:00.000+08:00|      100|           null|
|2024-11-28T10:00:00.000+08:00|      100|           85.0|
|2024-11-28T11:00:00.000+08:00|      100|           88.0|
|2024-11-29T10:00:00.000+08:00|      101|           85.0|
|2024-11-27T16:00:00.000+08:00|      101|           85.0|
+-----------------------------+---------+---------------+
Total line number = 8
It costs 0.081s

For more details on the date_bin function, refer to the Definition of Date Bin (Time Bucketing) feature documentation.

Example 2: Query the Latest Data Point for Each Device

SELECT device_id, LAST(temperature), LAST_BY(time, temperature)
  FROM table1
  GROUP BY device_id;

Result:

+---------+-----+-----------------------------+
|device_id|_col1|                        _col2|
+---------+-----+-----------------------------+
|      100| 90.0|2024-11-29T18:30:00.000+08:00|
|      101| 90.0|2024-11-30T14:30:00.000+08:00|
+---------+-----+-----------------------------+
Total line number = 2
It costs 0.078s

Example 3: Count Total Rows

Count the total number of rows for all devices:

SELECT COUNT(*) FROM table1;

Result:

+-----+
|_col0|
+-----+
|   18|
+-----+
Total line number = 1
It costs 0.060s

Count the total number of rows for each device:

SELECT device_id, COUNT(*) AS total_rows
  FROM table1
  GROUP BY device_id;

Result:

+---------+----------+
|device_id|total_rows|
+---------+----------+
|      100|         8|
|      101|        10|
+---------+----------+
Total line number = 2
It costs 0.060s

Example 4: Aggregate without a GROUP BY Clause

Query the maximum temperature across all devices:

SELECT MAX(temperature)
FROM table1;

Result:

+-----+
|_col0|
+-----+
| 90.0|
+-----+
Total line number = 1
It costs 0.086s

Example 5: Aggregate Results from a Subquery

Query the combinations of plants and devices where the average temperature exceeds 80.0 over a specified time range and has at least two records:

SELECT plant_id, device_id 
FROM (
    SELECT date_bin(10m, time) AS time, plant_id, device_id, AVG(temperature) AS temp FROM table1 WHERE time >= 2024-11-26 00:00:00 AND time <= 2024-11-29 00:00:00
    GROUP BY 1, plant_id, device_id
) 
WHERE temp > 80.0 
GROUP BY plant_id, device_id 
HAVING COUNT(*) > 1;

Result:

+--------+---------+
|plant_id|device_id|
+--------+---------+
|    1001|      101|
|    3001|      100|
+--------+---------+
Total line number = 2
It costs 0.073s
Last Updated:

Copyright © 2026 Timecho Limited All rights reserved.
Apache IoTDB and the Apache IoTDB project logo are trademarks of The Apache Software Foundation.