SQL Aggregate Functions

Updated on 2025-02-12 GMT+08:00

View PDF

Aggregate functions collect statistics on specified columns of structured logs and return a single value. They are often used with the SELECT and GROUP BY clauses. Table 1 lists the aggregate functions supported by LTS.

Pay attention to the following points when using aggregate functions:

Aggregate functions can be used in the SELECT clause of any query. You can use the syntax such as AGG(expr) FILTER(WHERE whereExpr) to filter columns before aggregation. The aggregate function aggregates only the columns that meet the filter criteria.
In the same SQL query statement, the result of the corresponding aggregate function varies according to the filter criteria.
Only COUNT function can be used with DISTINCT function.
Aggregation operations are not performed in a fixed order. When an SQL statement that contains multiple aggregate functions is executed for query, and the order for executing the functions affects the operation result, the results obtained from different queries may be inconsistent.
If the data to be aggregated is of the float type, the aggregation results may be inconsistent if you perform the same query for multiple times. If you want to obtain the same result from executing the same query, you are advised to use the ROUND function.

Syntax

SELECT COUNT(fieldname1)

Aggregate Function Statements

**Table 1** Aggregate function statements
Statement	Description	Example
COUNT(*)	Counts the number of rows.	SELECT COUNT(*)
COUNT(DISTINCT expr)	Counts the number of rows of a field after deduplication. The field value can be a character string or number. The returned value is an estimated value (with a default 2.3% standard error).	SELECT COUNT(DISTINCT host)
SUM(expr)	Returns the sum of the numbers.	SELECT SUM(visitCount)
MIN(expr)	Returns the minimum number.	SELECT MIN(visitCount)
MAX(expr)	Returns the maximum number.	SELECT MAX(visitCount)
AVG(expr)	Returns the average value.	SELECT AVG(visitCount)
EARLIEST(expr)	The expression must be of the numeric type.The earliest value of expr, which is the first queried value, is returned.	SELECT EARLIEST(visitCount)
LATEST(expr)	The expression must be of the numeric type. The latest value of expr, which is the last queried value, is returned.	SELECT LATEST(visitCount)
APPROX_QUANTILE_DS(expr, probability)	Calculates the approximate quantile of expr. The value of probability must be between 0 and 1.	APPROX_QUANTILE_DS(expr, probability)