Aggregate Functions

Aggregate functions compute a single result from a set of input rows. They are typically used with the GROUP BY clause in SELECT statements, but can also be used without GROUP BY to aggregate over all rows. When used in a SELECT with non-aggregate columns and no GROUP BY, the result is a single row.

All standard aggregate functions ignore NULL values (except count(*)). If every input value is NULL, the aggregate returns NULL, with the exception of count() (which returns 0) and total() (which returns 0.0).

Function Reference

Detailed Descriptions and Examples

The examples below use the following 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)

avg(X)

Returns the average of all non-NULL values of X as a REAL (floating-point) number. Returns NULL if all values are NULL.

Return type: REAL

SELECT avg(amount) FROM sales;
-- 175.0 (sum of non-NULL amounts / count of non-NULL amounts = 875.0 / 5)

SELECT region, avg(amount) AS avg_amount
FROM sales
GROUP BY region;

count(X) and count(*)

count(X)
count(*)

count(X) returns the number of rows where X is not NULL. count(*) returns the total number of rows in the group, including rows with NULL values.

Return type: INTEGER

SELECT count(*) FROM sales;
-- 6 (all rows)

SELECT count(amount) FROM sales;
-- 5 (excludes the row where amount is NULL)

SELECT region, count(*) AS total_rows, count(amount) AS rows_with_amount
FROM sales
GROUP BY region;

group_concat(X) and group_concat(X, Y)

group_concat(X)
group_concat(X, Y)

Concatenates all non-NULL values of X into a single string. The default separator is a comma (,). When Y is provided, it is used as the separator instead.

Return type: TEXT

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;

string_agg(X, Y)

string_agg(X, Y)

Alias for group_concat(X, Y). Provided for compatibility with PostgreSQL.

Return type: TEXT

SELECT region, string_agg(product, ', ') AS products
FROM sales
GROUP BY region;

max(X) and min(X)

max(X)
min(X)

max(X) returns the maximum non-NULL value of X. min(X) returns the minimum non-NULL value of X. Values are compared using the standard SQLite comparison rules. Returns NULL if all values are NULL.

Return type: Same as the input type

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;

sum(X) and total(X)

sum(X)
total(X)

Both functions return the sum of all non-NULL values of X. They differ in return type and behavior when all values are NULL.

Return type:

• sum(X): INTEGER if all non-NULL inputs are integers and no overflow occurs, otherwise REAL. Returns NULL if all values are NULL.
• total(X): Always 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, total: 24.0

Difference between sum() and total()

The key difference appears when all values in the group are NULL:

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

This makes total() convenient when you need a numeric result even for empty or all-NULL groups:

SELECT
    region,
    sum(amount) AS sum_amount,
    total(amount) AS total_amount
FROM sales
GROUP BY region;

-- total() is useful in arithmetic to avoid NULL propagation
SELECT total(amount) * 1.1 AS with_tax FROM sales;
-- 962.5

-- sum() with all NULLs would produce NULL, making the multiplication NULL too

Using Aggregates with GROUP BY

The GROUP BY clause partitions rows into groups. Each aggregate function is computed independently for each group.

SELECT
    region,
    product,
    count(*) AS order_count,
    sum(amount) AS total_sales,
    avg(amount) AS avg_sale,
    min(amount) AS min_sale,
    max(amount) AS max_sale
FROM sales
GROUP BY region, product;

Filtering Groups with HAVING

The HAVING clause filters groups after aggregation. Use WHERE to filter rows before aggregation and HAVING to filter groups after.

SELECT region, sum(amount) AS total_sales
FROM sales
WHERE amount IS NOT NULL
GROUP BY region
HAVING sum(amount) > 400;

Aggregates with DISTINCT

The DISTINCT keyword causes the aggregate to consider only unique non-NULL values.

SELECT count(product) FROM sales;
-- 6 (all non-NULL product values)

SELECT count(DISTINCT product) FROM sales;
-- 2 (only 'Widget' and 'Gadget')

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

Aggregates as Window Functions

All standard aggregate functions can be used as window functions. When used with an OVER clause, the function computes a running or partitioned result without collapsing rows.

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 Extension: stddev(X)

stddev(X)

Returns the population standard deviation of all non-NULL values of X. Returns NULL if there are no non-NULL values.

Return type: REAL

SELECT stddev(amount) FROM sales;

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

Extension Aggregate Functions

The following aggregate functions are available through the percentile extension. Load it before use.

median(X)

median(X)

Returns the median (middle value) of all non-NULL values of X.

Return type: REAL

SELECT median(amount) FROM sales;
-- 175.0

SELECT region, median(amount) AS median_amount
FROM sales
GROUP BY region;

percentile(Y, P)

percentile(Y, P)

Returns the P-th percentile of all non-NULL values of Y. Uses linear interpolation between adjacent values.

Return type: REAL

SELECT percentile(amount, 50) FROM sales;   -- 50th percentile (median)
SELECT percentile(amount, 90) FROM sales;   -- 90th percentile
SELECT percentile(amount, 25) FROM sales;   -- 25th percentile (Q1)

percentile_cont(Y, P) and percentile_disc(Y, P)

percentile_cont(Y, P)
percentile_disc(Y, P)

SQL-standard percentile functions. percentile_cont uses continuous (interpolated) distribution, while percentile_disc returns the nearest discrete input value.

Return type: REAL

-- Continuous percentile (interpolates between values)
SELECT percentile_cont(amount, 0.5) FROM sales;

-- Discrete percentile (returns an actual input value)
SELECT percentile_disc(amount, 0.5) FROM sales;

See Also

• Scalar Functions for per-row functions
• Expressions for operator syntax, CAST, CASE, and subqueries
• SELECT for GROUP BY, HAVING, and window function syntax