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 |
<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
tournaments table:
SELECT SUM(totalprizedollars) FROM tournaments;
Rows: 1Execution time: 6.43ms
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
