SQL-like query language for csv

Aggregate Functions

Aggregate functions calculate groupd records retrieved by a select query. If records are not grouped, all records are dealt with as one group.

If distinct option is specified, aggregate functions calculate only unique values.

Aggregate Functions can be used only in Select Clause, Having Clause and Order By Clause

name description
COUNT Return the number of values
MIN Return the minimum value
MAX Return the maximum value
SUM Return the sum of values
AVG Return the average of values
STDEV Return the sample standard deviation of values
STDEVP Return the population standard deviation of values
VAR Return the sample variance of values
VARP Return the population variance of values
MEDIAN Return the median of values
LISTAGG Return the concatenated string of values
JSON_AGG Return the string formatted in JSON array

Definitions

COUNT

COUNT([DISTINCT] expr)
expr
value
return
integer

Returns the number of non-null values of expr.

COUNT([DISTINCT] *)
return
integer

Returns the number of all values including null values.

MIN

MIN(expr)
expr
value
return
primitive type

Returns the minimum value of non-null values of expr. If all values are null, then returns null.

MAX

MAX(expr)
expr
value
return
primitive type

Returns the maximum value of non-null values of expr. If all values are null, then return a null.

SUM

SUM([DISTINCT] expr)
expr
value
return
float or integer

Returns the sum of float values of expr. If all values are null, then returns a null.

AVG

AVG([DISTINCT] expr)
expr
value
return
float or integer

Returns the average of float values of expr. If all values are null, then returns a null.

STDEV

STDEV([DISTINCT] expr)
expr
value
return
float or integer

Returns the sample standard deviation of float values of expr. If all values are null, then returns a null.

STDEVP

STDEVP([DISTINCT] expr)
expr
value
return
float or integer

Returns the population standard deviation of float values of expr. If all values are null, then returns a null.

VAR

VAR([DISTINCT] expr)
expr
value
return
float or integer

Returns the sample variance of float values of expr. If all values are null, then returns a null.

VARP

VARP([DISTINCT] expr)
expr
value
return
float or integer

Returns the population variance of float values of expr. If all values are null, then returns a null.

MEDIAN

MEDIAN([DISTINCT] expr)
expr
value
return
float or integer

Returns the median of float or datetime values of expr. If all values are null, then returns a null.

Even if expr represents datetime values, this function returns a float or integer value. The return value can be converted to a datetime value by using the DATETIME function.

LISTAGG

LISTAGG([DISTINCT] expr [, separator]) [WITHIN GROUP (order_by_clause)]
expr
value
separator
string
order_by_clause
Order By Clause
return
string

Returns the string result with the concatenated non-null values of expr. If all values are null, then returns a null.

separator is placed between values. Empty string is the default. By using order_by_clause, you can sort values.

JSON_AGG

JSON_AGG([DISTINCT] expr) [WITHIN GROUP (order_by_clause)]
expr
value
order_by_clause
Order By Clause
return
string

Returns the string formatted in JSON array of expr.