Lindorm Aggregate Functions Overview: Types, Syntax & Scenarios - Lindorm

Lindorm SQL supports aggregate functions for computing summaries across rows — totals, averages, extremes, and concatenated strings.

Applicable engines and versions

Engine	Version
LindormTable	All versions
LindormTSDB	V3.4.7 and later

HEAD and GROUP_CONCAT require LindormTable 2.7.9 or later and Lindorm SQL 2.8.8.0 or later.

Functions

Function	Description	Version requirement
AVG	Returns the average of all values in a column	All versions
COUNT	Returns the number of non-empty values in a column, or the total row count	All versions
MAX	Returns the maximum value in a column	All versions
MIN	Returns the minimum value in a column	All versions
SUM	Returns the sum of values in a column	All versions
HEAD	Returns the first non-empty value in a column or expression, with optional sort order	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+
GROUP_CONCAT	Concatenates column values within each group into a single string	LindormTable 2.7.9+, Lindorm SQL 2.8.8.0+

Sample table

The examples in this topic use the following sensor table:

-- Create a table named sensor.
CREATE TABLE sensor(
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

-- Write multiple rows of data at the same time.
UPSERT INTO sensor(device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6), ('F07A1261','south-cn','2021-04-22 15:33:00',18.1), ('F07A1261','south-cn','2021-04-22 15:33:10',19.7);

AVG

Returns the average of all values in the specified column.

Syntax

AVG(column_name)

Parameters

Parameter	Description
`column_name`	The column to average. Must be a numeric data type.

Example

Calculate the average temperature across all devices:

SELECT AVG(temperature) AS temperature FROM sensor;

Output:

+--------------------+
| temperature        |
+--------------------+
| 16.133333333333333 |
+--------------------+

COUNT

Returns the number of non-empty values in the specified column, or the total number of rows when called with *.

Syntax

COUNT(* | column_name)

Parameters

Parameter	Description
`column_name`	The column to count non-empty values in.
`*`	Counts all rows, including rows with null values.

Examples

Count the number of temperature readings:

SELECT COUNT(temperature) AS temperature FROM sensor;

Output:

+-------------+
| temperature |
+-------------+
| 3           |
+-------------+

Count rows where device_id is F07A1260:

SELECT COUNT(*) FROM sensor WHERE device_id = 'F07A1260';

Output:

+--------+
| EXPR$0 |
+--------+
| 1      |
+--------+

Count all rows in the table:

SELECT COUNT(*) FROM sensor;

Output:

+--------+
| EXPR$0 |
+--------+
| 3      |
+--------+

MAX

Returns the maximum value in the specified column.

Syntax

MAX(column_name)

Parameters

Parameter	Description
`column_name`	The column to evaluate.

Example

Get the highest temperature recorded across all devices:

SELECT MAX(temperature) FROM sensor;

Output:

+--------+
| EXPR$0 |
+--------+
| 19.7   |
+--------+

MIN

Returns the minimum value in the specified column.

Syntax

MIN(column_name)

Parameters

Parameter	Description
`column_name`	The column to evaluate.

Example

Get the lowest temperature recorded across all devices:

SELECT MIN(temperature) FROM sensor;

Output:

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

SUM

Returns the sum of values in the specified column.

Syntax

SUM(column_name)

Parameters

Parameter	Description
`column_name`	The column to sum. Must be a numeric data type.

Example

Calculate the total of all temperature readings:

SELECT SUM(temperature) FROM sensor;

Output:

+--------+
| EXPR$0 |
+--------+
| 48.4   |
+--------+

HEAD

Returns the first non-empty value in the column or expression specified by expr1. Rows are sorted by expr2 before selecting the first value. If expr2 is omitted, rows are sorted by the first primary key column.

Important

LindormTable 2.7.9 or later and Lindorm SQL 2.8.8.0 or later are required. Check your current versions in the console: LindormTable release notes and Lindorm SQL version description. To upgrade, see Update the minor engine version of a LindormTable instance.

Syntax

HEAD (expr1 [ ORDER BY expr2 [ DESC ] ] )

Parameters

Parameter	Description
`expr1`	The column or expression to retrieve the first non-empty value from. Supported data types: INTEGER, BIGINT, SMALLINT, TINYINT, FLOAT, DOUBLE, DECIMAL, BOOLEAN, VARCHAR, CHAR, TIMESTAMP, JSON, and GEOMETRY.
`expr2`	The column or expression to sort by. If omitted, rows are sorted by the first primary key column. Cannot be another aggregate function — for example, `HEAD(c1 ORDER BY COUNT(c2))` is not supported.

Examples

Get the minimum and maximum temperatures using ascending and descending sort:

SELECT HEAD(temperature ORDER BY temperature), HEAD(temperature ORDER BY temperature DESC) FROM sensor;

Output:

+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
| 10.6   | 19.7   |
+--------+--------+

Get the temperature for the device with the shortest device_id:

SELECT HEAD(temperature ORDER BY LENGTH(device_id)) FROM sensor;

Output:

+--------+
| EXPR$0 |
+--------+
| 10.6   |
+--------+

GROUP_CONCAT

Groups rows by the columns specified in a GROUP BY clause, sorts values within each group, and concatenates them into a single string.

Important

Syntax

GROUP_CONCAT ( [ DISTINCT ] expr (, expr ... )*
             [ ORDER BY col_name [ DESC ] (, col_name [ DESC ])* ]
             [ SEPARATOR str_val ] )

Important

DISTINCT and ORDER BY cannot be used together in the same GROUP_CONCAT call.

Parameters

Parameter	Description
`expr`	The projection column to concatenate.
`col_name`	The sorting column.
`str_val`	The separator between concatenated values.

Examples

The following examples use the t1 table:

-- Create a sample table named t1.
CREATE TABLE t1 (p1 INT, p2 VARCHAR, c1 INT, c2 VARCHAR, c3 DOUBLE, PRIMARY KEY(p1, p2));

-- Insert data into the table.
UPSERT INTO t1(p1, p2, c1, c2, c3) VALUES
(1, 'a', 10, 'java', 10.23),
(1, 'b', 10, 'c/c++', 5.76),
(1, 'c', 18, 'python', 8.21),
(2, 'a', 40, 'sql', 3.14),
(2, 'b', 100, 'c#', 4.96),
(2, 'c', 66, 'matlab', 7.01);

Example 1: Concatenate c1 values grouped by p1:

SELECT p1, GROUP_CONCAT(c1) FROM t1 GROUP BY p1;

Output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,100,66 |
+----+-----------+

Example 2: Concatenate distinct c1 values grouped by p1:

SELECT p1, GROUP_CONCAT(DISTINCT c1) FROM t1 GROUP BY p1;

Output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 18,10     |
| 2  | 100,40,66 |
+----+-----------+

Example 3: Concatenate c1 values grouped by p1, sorted ascending by c1:

SELECT p1, GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY p1;

Output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,10,18  |
| 2  | 40,66,100 |
+----+-----------+

Example 4: Concatenate c1 values grouped by p1, sorted descending by c3:

SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC) FROM t1 GROUP BY p1;

Output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10,18,10  |
| 2  | 66,100,40 |
+----+-----------+

Example 5: Same as Example 4, but with a pipe (|) separator:

SELECT p1, GROUP_CONCAT(c1 ORDER BY c3 DESC SEPARATOR '|') FROM t1 GROUP BY p1;

Output:

+----+-----------+
| p1 |  EXPR$1   |
+----+-----------+
| 1  | 10|18|10  |
| 2  | 66|100|40 |
+----+-----------+