SQLite Aggregate Functions

If no rows match your WHERE clause, avg() returns NULL, not 0. Use COALESCE(avg(X), 0) when a zero default is more useful than NULL — for example when displaying metrics in a dashboard where NULL would break downstream arithmetic.

In both SQLite and PostgreSQL, avg() ignores NULLs and returns NULL when all inputs are NULL (empty group). The key SQLite-specific behavior is that non-numeric strings are silently coerced to 0 rather than raising an error, which PostgreSQL would reject with a type error. PostgreSQL's avg() preserves exact numeric precision for integer inputs (returning NUMERIC), whereas SQLite always returns REAL (IEEE 754 float).

count(*) counts every row in the group, even those where every column is NULL. count(column) only counts rows where that specific column is non-NULL. Use count(*) to tally rows, and count(optional_column) to tally how many rows filled in an optional field. count(DISTINCT col) is useful for cardinality estimates (e.g., 'how many unique visitors?').

Behavior is identical between SQLite and PostgreSQL. Both count(*) count all rows, count(X) ignores NULLs, and count(DISTINCT X) counts distinct non-NULL values. One minor difference: in PostgreSQL you can use count(*) as a window function with OVER(), and SQLite supports the same via its window function syntax.

Without ORDER BY, the concatenation order is undefined and may change between queries or SQLite versions. Since SQLite 3.44.0 you can write group_concat(col ORDER BY col ASC) to guarantee consistent, sorted output. This is especially important when generating reproducible CSV exports or building delimited lists that will be compared across runs.

In PostgreSQL the primary name is string_agg(X, Y) and group_concat does not exist. In SQLite the primary name is group_concat and string_agg is an alias added in version 3.44.0. Additionally, PostgreSQL's string_agg requires a separator argument (Y is mandatory), while SQLite's group_concat defaults the separator to a comma when Y is omitted. The ORDER BY clause inside the aggregate is supported in both dialects, but was only added to SQLite in version 3.44.0.

If you write SQL that must run on both SQLite (3.44.0+) and PostgreSQL, prefer string_agg(X, Y) over group_concat(X, Y). The separator argument is required in string_agg(), matching PostgreSQL's requirement, which avoids the subtle difference where group_concat() silently defaults to a comma. Always supply an explicit separator to avoid surprises.

string_agg(X,Y) is an alias for group_concat(X,Y) added in SQLite 3.44.0 (2023-11-01) specifically for PostgreSQL and SQL Server compatibility. In PostgreSQL, string_agg is the canonical name and group_concat does not exist. The behavior is functionally identical once the alias was added, but note that SQLite's group_concat allows omitting the separator (defaulting to comma), while PostgreSQL's string_agg requires the separator — string_agg in SQLite also requires Y.

When your WHERE clause matches zero rows, max() returns NULL rather than raising an error. Use COALESCE(max(col), fallback) to substitute a safe default. Also remember that max() in SQLite follows type affinity: comparing TEXT '9' against INTEGER 10 may yield surprising results because '9' > 10 as text. Ensure your column has the correct affinity or CAST explicitly.

Aggregate max() behaves the same in SQLite and PostgreSQL: ignores NULLs, returns NULL on empty input. The scalar multi-argument form max(a, b, c) exists in SQLite as a core scalar function. PostgreSQL does not have a built-in scalar max() accepting multiple arguments — it uses the GREATEST() function instead. For ordering, SQLite uses its type affinity rules (NULL < INTEGER/REAL < TEXT < BLOB) while PostgreSQL uses strict type-based comparison with explicit casting required across types.

A common pattern is finding each user's or customer's first event. SELECT user_id, min(event_time) FROM events GROUP BY user_id is simpler and often faster than a correlated subquery or window function approach in SQLite, because SQLite can use an index on (user_id, event_time) to satisfy the group-wise min efficiently.

Aggregate min() behaves identically in SQLite and PostgreSQL: NULLs are ignored, and NULL is returned when all values are NULL or the group is empty. As with max(), SQLite provides a scalar multi-argument min(a, b, c) for finding the smallest of a set of values; PostgreSQL uses the LEAST() function for the same purpose. Type affinity differences apply the same way as for max().

The arithmetic mean is heavily influenced by extreme values (outliers). A single unusually large salary or response time can skew avg() considerably. median() always reflects the true middle of the dataset. Comparing avg() and median() side by side is a quick way to detect skew: if they differ significantly, your distribution is not symmetric and the median is usually the more representative central tendency.

PostgreSQL does not have a built-in median() aggregate function. The standard PostgreSQL approach is percentile_cont(0.5) WITHIN GROUP (ORDER BY X), which uses the SQL standard ordered-set aggregate syntax. SQLite's median(X) is simpler syntactically (standard aggregate syntax) and is equivalent to its own percentile_cont(X, 0.5). The SQLite form is more concise but is not SQL-standard and will not work in PostgreSQL.

SQLite offers both forms. percentile(salary, 95) and percentile_cont(salary, 0.95) return the same result — choose whichever reads more naturally in your context. The 0–100 scale of percentile() matches how people commonly communicate percentiles ('the 95th percentile'), making it more readable in application code and reports.

PostgreSQL uses the SQL standard ordered-set aggregate syntax: percentile_cont(0.95) WITHIN GROUP (ORDER BY salary). SQLite's percentile(salary, 95) is a simpler custom aggregate that does not follow the SQL standard ordered-set syntax. The P parameter in SQLite's percentile() is on a 0–100 scale, whereas PostgreSQL's percentile_cont uses 0.0–1.0. Both use continuous interpolation between data points. Code cannot be directly ported between the two without syntax changes.

percentile_cont(Y, 0.5) on values [1, 2, 3, 4] returns 2.5 (the interpolated midpoint), not 2 or 3. If you need the result to be an actual value from your dataset (for example, to return a real order ID or timestamp), use percentile_disc() instead, which always returns the smaller of the two boundary values.

PostgreSQL's percentile_cont uses the SQL standard ordered-set aggregate syntax: percentile_cont(0.5) WITHIN GROUP (ORDER BY salary). SQLite's percentile_cont(salary, 0.5) reverses the argument order (data column first, fraction second) and does not use the WITHIN GROUP syntax. Both produce identical numeric results for the same inputs. The P range is 0.0–1.0 in both SQLite's percentile_cont and PostgreSQL's version.

percentile_disc() guarantees the result is a value that actually exists in your dataset. This matters when the percentile will be used to look up a specific record (e.g., 'find the order whose total is at the 90th percentile'), or when the domain is discrete and interpolation is meaningless (e.g., whole-number ratings from 1–5). For continuous measures like latency or salary where fractional values are meaningful, prefer percentile_cont().

PostgreSQL's percentile_disc uses ordered-set aggregate syntax: percentile_disc(0.5) WITHIN GROUP (ORDER BY salary). SQLite's percentile_disc(salary, 0.5) places the data column first. Both return the smaller of the two boundary values when the percentile falls between data points. The return type of percentile_disc matches the input type (preserving INTEGER if input is INTEGER), whereas percentile_cont always returns REAL due to interpolation.

The most common source of unexpected NULLs in aggregation is sum() over a filtered result set that matches no rows. This is SQL-standard behavior. If a zero result is more useful than NULL in your application (e.g., computing a running balance or displaying a metric), either switch to total(X) which always returns 0.0 for empty sets, or wrap with COALESCE(sum(X), 0). Note that sum() preserves integer type while total() always returns REAL.

In PostgreSQL, sum() also returns NULL on an empty set and follows SQL standard behavior. Unlike PostgreSQL, SQLite's sum() silently coerces non-numeric strings to 0 instead of raising a type error. PostgreSQL preserves exact precision for INTEGER and NUMERIC inputs (using NUMERIC arithmetic), whereas SQLite switches to IEEE 754 REAL as soon as any input is non-integer, which can introduce floating-point imprecision. PostgreSQL does not have a total() equivalent — COALESCE(sum(x), 0) is the idiomatic pattern there.

total() solves the common annoyance of sum() returning NULL on empty input. It is particularly useful in expressions where NULL would propagate and ruin downstream arithmetic (e.g., computing a balance as total(credits) - total(debits)). Note that total() always returns REAL, so if you need an integer result you'll need to CAST. Also note that total() is not standard SQL and does not exist in PostgreSQL — use COALESCE(sum(x), 0) for portability.

total() is a SQLite-specific function with no equivalent in PostgreSQL. It was added to work around the SQL-standard behavior of sum() returning NULL on empty input. In PostgreSQL, the standard pattern is COALESCE(sum(x), 0). Additionally, total() always returns REAL in SQLite, which means it avoids integer overflow exceptions that sum() can raise. PostgreSQL's sum() over integer columns returns exact BIGINT or NUMERIC, preserving precision that total()'s REAL return type cannot guarantee for very large integers.