Aggregate Functions

Aggregate functions combine values from multiple rows into one result. With GROUP BY, they return results per group. NULL handling depends on the function; FIRST/LAST preserve a selected NULL return value.

Quick Reference

COUNT

Counts records. COUNT(*) returns all rows, including NULLs; COUNT(col) returns the count of non-NULL rows.

COUNT(*)
COUNT(column_name)

Mach> CREATE LOG TABLE count_table (id1 INTEGER, id2 INTEGER);
Mach> INSERT INTO count_table VALUES(1, 1);
Mach> INSERT INTO count_table VALUES(2, 2);
Mach> INSERT INTO count_table VALUES(null, 4);

Mach> SELECT COUNT(*) FROM count_table;
COUNT(*)
---------
3

Mach> SELECT COUNT(id1) FROM count_table;
COUNT(id1)
-----------
2

SUM

Returns the sum of a numeric column.

SUM(column_name)

Mach> SELECT c1, SUM(c2) FROM sum_table GROUP BY c1;
c1          SUM(c2)
--------------------
1           6
2           6
3           4

AVG

Returns the average of a numeric column.

AVG(column_name)

Mach> SELECT id1, AVG(id2) FROM avg_table GROUP BY id1;
id1         AVG(id2)
---------------------
1           2
2           2
NULL        4

MIN

Returns the minimum of the specified numeric column.

MIN(column_name)

Mach> SELECT MIN(c1) FROM min_table;
MIN(c1)
--------
1

MAX

Returns the maximum of the specified numeric column.

MAX(column_name)

Mach> SELECT MAX(c) FROM max_table;
MAX(c)
-------
30

STDDEV / STDDEV_POP

Returns the sample standard deviation (STDDEV) or population standard deviation (STDDEV_POP) of a column.

STDDEV(column)
STDDEV_POP(column)

Mach> SELECT c2, STDDEV(c1) FROM stddev_table GROUP BY c2;
c2          STDDEV(c1)
-----------------------
1           0.707107
2           0.707107

Mach> SELECT c2, STDDEV_POP(c1) FROM stddev_table GROUP BY c2;
c2          STDDEV_POP(c1)
---------------------------
1           0.5
2           0.5

VARIANCE / VAR_POP

Returns sample variance (VARIANCE) or population variance (VAR_POP).

VARIANCE(column_name)
VAR_POP(column_name)

Mach> SELECT VARIANCE(c1) FROM var_table;
VARIANCE(c1)
--------------
0.333333

Mach> SELECT VAR_POP(c1) FROM var_table;
VAR_POP(c1)
-------------
0.25

FIRST / LAST

Returns return_expr from the first (FIRST) or last (LAST) row in each group ordered by sort_expr. Useful for retrieving values at specific points in a time series.

FIRST(sort_expr, return_expr)
LAST(sort_expr, return_expr)

Composite sort keys

Composite FIRST/LAST sort keys and ordered EXTENSION ROLLUPs are supported from Machbase 8.7.3. Edition-specific restrictions are described below.

FIRST((key1 [ASC | DESC], key2 [ASC | DESC], ...), return_expr)
LAST ((key1 [ASC | DESC], key2 [ASC | DESC], ...), return_expr)

Specify two or more keys in parentheses as the first argument. Keys are compared from left to right; the next key is considered only when preceding keys are equal. The default direction is ASC. FIRST returns the expression from the first row in that order, and LAST from the last row. The syntax does not limit the number of keys to three.

• Rows with a NULL first key are excluded. NULL secondary keys follow the type’s ORDER BY comparison rules.
• A NULL return value in the selected row is returned unchanged.
• Equal values for every key do not identify a particular row. Deterministic selection requires a unique business key.
• Integer types, FLOAT/DOUBLE, DECIMAL, DATETIME, VARCHAR, IPV4/IPV6 sort keys are supported. BINARY keys and DISTINCT with composite keys are not supported.
• Ordinary SELECT keys are not restricted to BASETIME and may be comparable expressions such as seq + 1. Ordered EXTENSION definitions and ROLLUP queries use source columns only.
• An outer ORDER BY sorts output rows; it does not replace the function’s selection criteria.
• Additional key reads, comparisons, copies, and state incur costs; unchanged performance is not guaranteed.

event_id distinguishes rows with equal time and seq. The time direction can remain ASC while event_id uses DESC.

SELECT code,
       FIRST((time, seq, event_id), price) AS first_price,
       LAST((time ASC, seq ASC, event_id DESC), price) AS last_price
  FROM raw_tab
 GROUP BY code;

Different key directions in SELECT and ORDER BY select independent candidates. Use the output alias to sort by the displayed aggregate.

SELECT code, FIRST((time, seq, event_id), price) AS first_price
  FROM raw_tab
 GROUP BY code
 ORDER BY FIRST((time, seq, event_id DESC), price);

From 8.7.3, Standard supports an explicit public ROWID as a second or later key, such as FIRST((time, ROWID), price). It identifies the selected source row, not business or insertion order. Cluster does not support public ROWID keys. See the ROWID example and restrictions.

Complete data and ordered ROLLUP example.

Single-key example

Mach> SELECT group_no, FIRST(id, name) FROM firstlast_table GROUP BY group_no;
group_no    first(id, name)
----------------------------
0           John
1           Grey

Mach> SELECT group_no, LAST(id, name) FROM firstlast_table GROUP BY group_no;
group_no    last(id, name)
---------------------------
0           Ryan
1           Kyle

SUMSQ

Returns the sum of squared numeric values.

SUMSQ(value)

Mach> SELECT c1, SUMSQ(c2) FROM sumsq_table GROUP BY c1;
c1          SUMSQ(c2)
----------------------
1           14
2           41

MEDIAN

Returns the exact median of a numeric expression.

MEDIAN(value)

SELECT MEDIAN(temp_c) FROM sensor_log;

MODE

Returns the most frequent numeric value. If multiple values tie, returns the smallest.

MODE(value)

SELECT MODE(alarm_code) FROM event_log;

AREA

Calculates the area under a curve of numeric (x, y) points using trapezoidal integration. Returns NULL with fewer than two valid points.

AREA(y, x)

SELECT AREA(power_kw, sample_sec) FROM power_log;

SLOPE

Calculates the slope of the linear regression line for numeric (x, y) points. Returns NULL if x has zero variance or there are insufficient valid data.

SLOPE(y, x)

SELECT SLOPE(temp_c, sample_sec) FROM sensor_log;

GROUP_CONCAT

Concatenates column values within a group into a string.

GROUP_CONCAT(
    [DISTINCT] column
    [ORDER BY column [ASC | DESC] [, ...]]
    [SEPARATOR str_val]
)

Mach> SELECT GROUP_CONCAT(name) FROM concat_table GROUP BY id2;
G_NAMES
---------
Jack,Jack,Ram
Jill,Zara,John

Mach> SELECT GROUP_CONCAT(DISTINCT name SEPARATOR '.') FROM concat_table GROUP BY id2;
G_NAMES
---------
Jack.Ram
Jill.Zara.John

TS_CHANGE_COUNT

Returns the number of changes in time-ordered column values. VARCHAR is not supported.

TS_CHANGE_COUNT(column)

Mach> SELECT id, TS_CHANGE_COUNT(ip) FROM ipcount_table GROUP BY id;
id          TS_CHANGE_COUNT(ip)
--------------------------------
1           4
2           2

TOP_K

Returns the k most frequent numeric values as a value:count string, ordered by descending frequency and ascending value for ties.

TOP_K(value, k)

SELECT TOP_K(alarm_code, 3) FROM event_log;
-- Example result: 101:532,205:317,301:90

PERCENTILE_CONT / PERCENTILE_DISC

Aggregate functions that calculate exact percentiles. ratio must be a constant from 0.0 through 1.0.

• PERCENTILE_CONT: Interpolates between adjacent sorted values.
• PERCENTILE_DISC: Selects an observed value.

PERCENTILE_CONT(value, ratio)
PERCENTILE_DISC(value, ratio)

Shorthand functions P05, P10, P90, and P95 are also available.

SELECT PERCENTILE_CONT(latency_ms, 0.95) AS p95,
       PERCENTILE_DISC(latency_ms, 0.50) AS p50
FROM api_log;

-- Shorthand
SELECT P05(response_ms), P95(response_ms) FROM web_log;

APPROX_PERCENTILE

Approximate percentile function, useful for very large datasets when a small error is acceptable. Shorthands include APPROX_MEDIAN, APPROX_P05, APPROX_P10, APPROX_P90, and APPROX_P95.

APPROX_PERCENTILE(value, ratio)
APPROX_MEDIAN(value)
APPROX_P95(value)

SELECT APPROX_PERCENTILE(latency_ms, 0.95) AS ap95,
       APPROX_MEDIAN(latency_ms)            AS amedian
FROM api_log;

CUME_DIST

Returns the cumulative fraction of rows whose value is at most threshold (0.0–1.0). This is an aggregate function, not a window function.

CUME_DIST(value, threshold)

SELECT CUME_DIST(latency_ms, 100) FROM api_log;