Skip to main content
Aggregate functions compute a single result from a set of input rows. They are typically used with GROUP BY, but can also be applied to an entire table without it. All standard aggregate functions ignore NULL values (except count(*)). When every input is NULL, the aggregate returns NULL — except for count() (returns 0) and total() (returns 0.0).

​
Function Reference

FunctionReturn TypeDescription
avg(X)REALAverage of all non-NULL values of X
count(X)INTEGERNumber of rows where X is not NULL
count(*)INTEGERTotal number of rows in the group
group_concat(X)TEXTConcatenation of non-NULL values of X, comma-separated
group_concat(X, Y)TEXTConcatenation of non-NULL values of X, separated by Y
string_agg(X, Y)TEXTAlias for group_concat(X, Y) (PostgreSQL compatibility)
max(X)same as XMaximum non-NULL value of X
min(X)same as XMinimum non-NULL value of X
sum(X)INTEGER or REALSum of non-NULL values. Returns NULL if all values are NULL
total(X)REALSum of non-NULL values. Always returns REAL; returns 0.0 if all values are NULL
array_agg(X)BLOB (array)Collects all values into an array (Turso extension)
stddev(X)REALPopulation standard deviation (Turso extension)

​
Examples

The examples below use this table: 
CREATE TABLE sales (
    id       INTEGER PRIMARY KEY,
    region   TEXT,
    product  TEXT,
    amount   REAL,
    quantity INTEGER
);

INSERT INTO sales VALUES
    (1, 'North', 'Widget', 100.00, 5),
    (2, 'North', 'Gadget', 250.00, 2),
    (3, 'South', 'Widget', 150.00, 8),
    (4, 'South', 'Gadget', NULL,   3),
    (5, 'North', 'Widget', 200.00, NULL),
    (6, 'South', 'Widget', 175.00, 6);

​
avg(X)

Returns the average of all non-NULL values as REAL. Returns NULL if all values are NULL. 
SELECT avg(amount) FROM sales;
-- 175.0

SELECT region, avg(amount) AS avg_amount
FROM sales
GROUP BY region;
regionavg_amount
North183.33…
South162.5
avg(X) ignores NULL values in both the sum and the count. The NULL amount in the South region is excluded from both numerator and denominator.

​
count(X) / count(*)

count(X) counts rows where X is not NULL. count(*) counts all rows including those with NULL values. 
SELECT count(*) FROM sales;       -- 6 (all rows)
SELECT count(amount) FROM sales;  -- 5 (excludes the NULL row)

SELECT region, count(*) AS total_rows, count(amount) AS non_null
FROM sales
GROUP BY region;
regiontotal_rowsnon_null
North33
South32

​
group_concat(X) / group_concat(X, Y)

Concatenates non-NULL values into a single string. Default separator is a comma. string_agg(X, Y) is an alias. 
SELECT group_concat(product) FROM sales;
-- 'Widget,Gadget,Widget,Gadget,Widget,Widget'

SELECT group_concat(DISTINCT product) FROM sales;
-- 'Widget,Gadget'

SELECT region, group_concat(product, ' | ') AS products
FROM sales
GROUP BY region;
regionproducts
NorthWidget | Gadget | Widget
SouthWidget | Gadget | Widget

​
max(X) / min(X)

Return the maximum or minimum non-NULL value. Returns NULL if all values are NULL. 
SELECT max(amount), min(amount) FROM sales;
-- max: 250.0, min: 100.0

SELECT region, max(amount) AS highest, min(amount) AS lowest
FROM sales
GROUP BY region;
regionhighestlowest
North250.0100.0
South175.0150.0
When called with a single argument in aggregate context, max(X) and min(X) act as aggregate functions. When called with two or more arguments (e.g. max(a, b, c)), they act as scalar functions.

​
sum(X) / total(X)

Both return the sum of non-NULL values. They differ in return type and NULL behavior.
  • sum(X): Returns INTEGER when all inputs are integers and no overflow occurs; otherwise REAL. Returns NULL if all values are NULL.
  • total(X): Always returns REAL. Returns 0.0 if all values are NULL.
SELECT sum(amount), total(amount) FROM sales;
-- sum: 875.0, total: 875.0

SELECT sum(quantity), total(quantity) FROM sales;
-- sum: 24 (INTEGER), total: 24.0 (REAL)
Difference with all-NULL groups: 
CREATE TABLE empty_amounts (val REAL);
INSERT INTO empty_amounts VALUES (NULL), (NULL);

SELECT sum(val) FROM empty_amounts;    -- NULL
SELECT total(val) FROM empty_amounts;  -- 0.0
Use total() when you need a numeric result even for empty or all-NULL groups: 
SELECT total(amount) * 1.1 AS with_tax FROM sales;
-- 962.5  (always a number, never NULL)

​
GROUP BY and HAVING

SELECT
    region,
    product,
    count(*) AS order_count,
    sum(amount) AS total_sales,
    avg(amount) AS avg_sale
FROM sales
GROUP BY region, product;
Filter groups after aggregation with HAVING: 
SELECT region, sum(amount) AS total_sales
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
HAVING sum(amount) > 400;

​
DISTINCT

The DISTINCT keyword causes the aggregate to consider only unique non-NULL values: 
SELECT count(DISTINCT product) FROM sales;      -- 2
SELECT group_concat(DISTINCT product) FROM sales; -- 'Widget,Gadget'

​
Aggregates as Window Functions

All standard aggregate functions can be used with an OVER clause as window functions: 
SELECT
    id,
    region,
    amount,
    sum(amount)  OVER (PARTITION BY region ORDER BY id) AS running_total,
    count(*)     OVER (PARTITION BY region)              AS region_count
FROM sales
ORDER BY region, id;

​
Turso Extensions

​
array_agg(X)

array_agg(X) is a Turso extension and is not part of standard SQLite.
Collects all values of X (including NULLs) into an array. Returns NULL for empty groups. 
SELECT array_agg(name) FROM users;
-- ["Alice","Bob","Charlie"]

SELECT department, array_agg(name)
FROM employees
GROUP BY department;
Control ordering with a subquery: 
SELECT array_agg(name)
FROM (SELECT name FROM users ORDER BY name);

​
stddev(X)

stddev(X) is a Turso extension and is not part of standard SQLite.
Returns the population standard deviation of all non-NULL values of X. Returns NULL if there are no non-NULL values. 
SELECT stddev(amount) FROM sales;

SELECT region, avg(amount) AS mean, stddev(amount) AS std_dev
FROM sales
GROUP BY region;

​
Percentile Extension

The following aggregate functions are available through the percentile extension.
These functions require the percentile extension. Load it with SELECT load_extension('./percentile'); or by configuring auto-load on your connection.

​
median(X)

Returns the median (middle value) of all non-NULL values of X. 
SELECT median(amount) FROM sales;
-- 175.0

​
percentile(Y, P)

Returns the P-th percentile (0.0–100.0) of all non-NULL values of Y, using linear interpolation. 
SELECT percentile(amount, 50) FROM sales;  -- 50th percentile (median)
SELECT percentile(amount, 90) FROM sales;  -- 90th percentile
SELECT percentile(amount, 25) FROM sales;  -- Q1

​
percentile_cont(Y, P) / percentile_disc(Y, P)

SQL-standard percentile functions. percentile_cont uses continuous (interpolated) distribution. percentile_disc returns the nearest discrete input value.
Note the range difference: percentile(Y, P) takes P from 0 to 100, while percentile_cont and percentile_disc take P from 0.0 to 1.0.
SELECT percentile_cont(amount, 0.5) FROM sales;  -- interpolated median
SELECT percentile_disc(amount, 0.5) FROM sales;  -- discrete median

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us