ARRAY_SUM

Return the sum of the numeric elements of an array.

Category: collectionReturns: NUMERICDialect: PostgreSql

Syntax

ARRAY_SUM(array)

Description

## Overview Computes the arithmetic sum of all non-NULL numeric elements in an array. ARRAY_SUM is the array-level analogue of the scalar SUM aggregate and is the preferred way to collapse a per-row numeric array to a single total without an EXPLODE-and-group rewrite. Typical uses include computing the total duration or size represented by a per-session array, summing a component-wise breakdown stored inline, and producing denormalised KPI columns that describe the total magnitude of a nested collection. ## Behavior - Returns a numeric value whose type is promoted from the element type: INT-backed arrays return BIGINT (to avoid narrow overflow), FLOAT and DOUBLE return DOUBLE, DECIMAL arrays return DECIMAL with scale adjusted per standard summation rules. - NULL elements are skipped; they do not contribute to the total. - Empty input array returns 0 (of the appropriate numeric type). - Works only on numeric element types. Arrays of non-numeric elements raise a type error. - Computation is non-destructive; the input is not modified. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns 0, not NULL. - Array of all NULL elements returns 0 (sum over an empty subset). - Mixed arrays sum only the non-NULL subset. ## Compatibility - Equivalent to UNNEST followed by scalar SUM, without materialising a relation. - Floating-point accumulation uses pairwise summation where supported. Very large arrays of widely differing magnitudes can still incur minor rounding.

Parameters

Name	Type	Description
`array`		Specifies the numeric array whose elements are summed. Accepts INT, BIGINT, FLOAT, DOUBLE, or DECIMAL elements. NULL elements are excluded from the total.

Examples

-- Sum integers
SELECT ARRAY_SUM(ARRAY[1, 2, 3, 4]);  -- 10

-- Sum a per-session measurement array
SELECT session_id, ARRAY_SUM(page_load_ms) AS total_load_ms
FROM analytics.events.user_sessions;

-- Sum with decimals
SELECT ARRAY_SUM(ARRAY[1.5, 2.5, 3.0]);  -- 7.0

-- NULL elements are ignored
SELECT ARRAY_SUM(ARRAY[10, CAST(NULL AS INT), 30]);  -- 40

-- Empty array returns 0
SELECT ARRAY_SUM(CAST(ARRAY() AS ARRAY<INT>));  -- 0

-- NULL input array returns NULL
SELECT ARRAY_SUM(CAST(NULL AS ARRAY<INT>));  -- NULL

Pitfalls

Empty arrays and all-NULL arrays both return 0, which may mask missing data. Check ARRAY_SIZE or use COALESCE with a sentinel when 0 and missing must be distinguished.
INT element arrays are widened to BIGINT for the sum. For DECIMAL arithmetic, cast the array or its elements explicitly to DECIMAL(p, s) before calling.
Floating-point sums can drift for arrays with mixed magnitudes. Use DECIMAL element types for regulated totals.
ARRAY_SUM requires a numeric element type. Arrays of STRING or BOOLEAN raise a type error; cast elements with TRY_CAST upstream if the column might contain mixed data.