SUM()
window function returns the sum of the input column or expression values. It can be used with a RANGE
clause, that allows you to define a logical frame of rows based on the values of the current row, rather than a fixed number of rows.
SUM
window function are INTEGER
, BIGINT
, REAL
and DOUBLE PRECISION
. The return types of the SUM
function are: BIGINT
for integer and DOUBLE PRECISION
for floating-point arguments.
SUM()
window function works with numeric values and ignores NULL onesexpression
: input’s column or expression values to be summedPARTITION BY
: optional clause, which divides the result set into partitions to which the function is appliedROWS | RANGE BETWEEN
: range-based window frame relative to the current rowwinsales
table that stores details of some sales transactions:
SUM()
window function with ORDER BY
keyword:
qty
ordered by dateid and salesid using a ROWS UNBOUNDED PRECEDING
frame,
which sums all rows from the start up to the current row:
running_qty_sum
column shows the cumulative sum of qty
ordered by dateid
and salesid
.
For each row, it sums all qty
values from the first row up to the current row in that order.
SUM()
function with ORDER BY
keyword and PARTITION BY
clause:
qty
of all sales within the 30-day window ending at the current row’s dateid
: