SUM

Calculates the sum of an expression.

Syntax

SUM ([ DISTINCT ] <value>) [FILTER ([WHERE] <condition>)]

Parameters

Parameter	Description	Supported input types
`<value>`	The expression used to calculate the sum.	Any numeric type
`<condition>`	An optional boolean expression to filter rows used in aggregation	`BOOL`

Valid values for <value> include column names or expressions that evaluate to numeric values. When DISTINCT is being used, only the unique number of rows with no NULL values are summed.

Return Types

NUMERIC

Precision and Determinism

Applying SUM to REAL and DOUBLE PRECISION is subject to floating point arithmetic accuracy limitations and its resulting error. This error may add up when aggregating multiple values. The order of operations while computing the aggregate is non-deterministic. This can lead to varying total floating point error when running a query multiple times. If this is not acceptable for your use-case, aggregate on NUMERIC data instead.

Examples

The tournaments table contains the following data:

SELECT name, totalprizedollars FROM tournaments ORDER BY name LIMIT 5;

name text null	totalprizedollars int null
The Acceleration Championship	19274
The Acceleration Trials	13877
The Accelerator Cup	19466
The African Grand Prix	895
The Alpine Adventure Rally	16810

Rows: 5Execution time: 6.00ms

The following example calculates the total prize money across all tournaments in the tournaments table:

SELECT SUM(totalprizedollars) FROM tournaments;

sum long null
2026880

Rows: 1Execution time: 6.43ms

The levels table contains the following maxpoints values:

SELECT level, maxpoints FROM levels ORDER BY level;

level int null	maxpoints int null
1	20
2	30
3	40
4	100
5	150
6	80
7	70
8	100
9	250
10	500

Rows: 10Execution time: 6.00ms

SUM(DISTINCT) adds only unique values, skipping duplicates. maxpoints of 100 is shared by two levels (Nitro Narrows and Racing Ravine), so SUM(DISTINCT maxpoints) counts that value only once:

SELECT SUM(maxpoints) AS total_maxpoints, SUM(DISTINCT maxpoints) AS distinct_maxpoints FROM levels;

total_maxpoints long null	distinct_maxpoints long null
1340	1240

Rows: 1Execution time: 6.08ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Precision and Determinism

Examples

​Syntax

​Parameters

​Return Types

​Precision and Determinism

​Examples

Syntax

Parameters

Return Types

Precision and Determinism

Examples