SUM OVER

Reference material for SUM function

Calculate the sum of the values within the requested window.

The SUM function works with numeric values and ignores NULL values.

For more information on usage, please refer to Window Functions.

Syntax

SUM([ DISTINCT ] <value> ) OVER ( [ PARTITION BY <partition_by> ] )

Parameters

Parameter	Description	Supported input types
<value>	The expression used for the SUM function	Any numeric type
<partition_by>	An expression used for the PARTITION BY clause	Any

Return Types

NUMERIC

When DISTINCT is specified, duplicate values from <expression> are removed before calculating the sum.

Example

The example below shows each player’s score alongside the total score for their level. Unlike a regular SUM() aggregation, the window function keeps one row per player while computing the per-level total.

SELECT
    nickname,
    level,
    score,
    SUM(score) OVER (PARTITION BY level) AS level_total
FROM
    (VALUES
        ('kennethpark', 9, 76),
        ('sabrina21', 9, 90),
        ('burchdenise', 10, 79),
        ('ymatthews', 10, 85),
        ('rileyjon', 10, 80)
    ) AS t(nickname, level, score)
ORDER BY level, nickname;

nickname text	level int	score int	level_total long null
kennethpark	9	76	166
sabrina21	9	90	166
burchdenise	10	79	244
rileyjon	10	80	244
ymatthews	10	85	244

Rows: 5Execution time: 1.52ms