Aggregate

Count(*) Counts

Count(*) counts every row.

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: 3});

select count(*) from T as t;

Result

[
  3
]

Count(expr) Counts

Count(expr) counts only rows where expr is non-null.

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: null});

select count(t.x) from T as t;

Result

[
  2
]

Count(*) Includes

Count(*) includes rows with a null column, unlike count(expr).

SQL

create table T;

insert into T ({x: 1}, {x: null});

select count(*) from T as t;

Result

[
  2
]

Sum Of Integers

Sum of integers.

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: 3});

select sum(t.x) from T as t;

Result

[
  6
]

Sum Of

Sum of floats (and mixed int/float) is a float.

SQL

create table T;

insert into T ({x: 1.5}, {x: 2}, {x: 0.25});

select sum(t.x) from T as t;

Result

[
  3.75
]

Sum Skips

Sum skips null inputs.

SQL

create table T;

insert into T ({x: 10}, {x: null}, {x: 5});

select sum(t.x) from T as t;

Result

[
  15
]

An Integer

An integer sum that overflows i64 promotes to a float (sqlite-faithful).

SQL

create table T;

insert into T ({x: 9223372036854775807}, {x: 9223372036854775807});

select sum(t.x) from T as t;

Result

[
  18446744073709552000
]

Overflow Errors

A float sum that overflows to non-finite is a runtime error, not a silent null.

SQL

create table T;

insert into T ({x: 1e308}, {x: 1e308});

select sum(t.x) from T as t;

Expected error: runtime

Min Of Integers

Min of integers.

SQL

create table T;

insert into T ({x: 3}, {x: 1}, {x: 2});

select min(t.x) from T as t;

Result

[
  1
]

Max Of Integers

Max of integers.

SQL

create table T;

insert into T ({x: 3}, {x: 1}, {x: 2});

select max(t.x) from T as t;

Result

[
  3
]

Min Of

Min of strings is lexicographic.

SQL

create table T;

insert into T ({s: "banana"}, {s: "apple"}, {s: "cherry"});

select min(t.s) from T as t;

Result

[
  "apple"
]

Max Of

Max of strings is lexicographic.

SQL

create table T;

insert into T ({s: "banana"}, {s: "apple"}, {s: "cherry"});

select max(t.s) from T as t;

Result

[
  "cherry"
]

Min/max Skip

Min/max skip null inputs.

SQL

create table T;

insert into T ({x: 2}, {x: null}, {x: 5});

select min(t.x) from T as t;

Result

[
  2
]

Avg Returns

Avg returns a float mean.

SQL

create table T;

insert into T ({x: 1}, {x: 2});

select avg(t.x) from T as t;

Result

[
  1.5
]

Avg Divides

Avg divides the sum by the non-null count only.

SQL

create table T;

insert into T ({x: 2}, {x: null}, {x: 4});

select avg(t.x) from T as t;

Result

[
  3
]

Count(*) Over

Count(*) over an empty table is 0 (one row).

SQL

create table T;

select count(*) from T as t;

Result

[
  0
]

Sum Over

Sum over an empty table is null (one row).

SQL

create table T;

select sum(t.x) from T as t;

Result

[
  null
]

Min Over

Min over an empty table is null (one row).

SQL

create table T;

select min(t.x) from T as t;

Result

[
  null
]

Max Over

Max over an empty table is null (one row).

SQL

create table T;

select max(t.x) from T as t;

Result

[
  null
]

Avg Over

Avg over an empty table is null (one row).

SQL

create table T;

select avg(t.x) from T as t;

Result

[
  null
]

Aggregates Over

Aggregates over an all-null column — count(expr) 0, others null.

SQL

create table T;

insert into T ({x: null}, {x: null});

select count(t.x) from T as t;

Result

[
  0
]

SQL

create table T;

insert into T ({x: null}, {x: null});

select count(t.x) from T as t;

select sum(t.x) from T as t;

Result

[
  null
]

SQL

create table T;

insert into T ({x: null}, {x: null});

select count(t.x) from T as t;

select sum(t.x) from T as t;

select max(t.x) from T as t;

Result

[
  null
]

SQL

create table T;

insert into T ({x: null}, {x: null});

select count(t.x) from T as t;

select sum(t.x) from T as t;

select max(t.x) from T as t;

select avg(t.x) from T as t;

Result

[
  null
]

Aggregation Runs

Aggregation runs over the post-where stream.

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: 3});

select count(*) from T as t where t.x > 1;

Result

[
  2
]

Several Aggregates

Several aggregates project into one object row.

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: 3});

select { c: count(*), s: sum(t.x), m: max(t.x) } from T as t;

Result

[
  { "c": 3, "s": 6, "m": 3 }
]

Arithmetic Over

Arithmetic over an aggregate is allowed (the agg is folded, then combined).

SQL

create table T;

insert into T ({x: 1}, {x: 2}, {x: 3});

select sum(t.x) + 1 from T as t;

Result

[
  7
]

Limit Applies

Limit applies to the single aggregate row.

SQL

create table T;

insert into T ({x: 1}, {x: 2});

select count(*) from T as t limit 0;

Result

[]

An Aggregate

An aggregate in WHERE is a static (bind) error.

SQL

create table T;

select count(*) from T as t where count(*) > 0;

Expected error: static

An Aggregate

An aggregate nested inside another is a static (bind) error.

SQL

create table T;

select sum(count(t.x)) from T as t;

Expected error: static

Only Count

Only count accepts the star form; sum(*) is a static error.

SQL

create table T;

select sum(*) from T as t;

Expected error: static

Column Rejected

A bare column reference alongside an aggregate is unsupported (no GROUP BY).

SQL

create table T;

select { c: count(*), x: t.x } from T as t;

Expected error: static

Min Over

Min over incomparable types (int vs string) is a runtime error.

SQL

create table T;

insert into T ({v: 1}, {v: "a"});

select min(t.v) from T as t;

Expected error: runtime