Neoview SQL Reference Manual (R2.5)

SUM Window Function
SUM is a window function that returns the sum of non null values of the given expression for
the current window specified by the inline-window-specification.
SUM ([ALL] expression) OVER (inline-window-specification)
inline-window-specification is:
[PARTITION BY expression [, expression]...]
[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
[,expression [ASC[ENDING] | DESC[ENDING]]]...]
[ window-frame-clause ]
window-frame-clause is:
ROWS CURRENT ROW
| ROWS preceding-row
| ROWS BETWEEN preceding-row AND preceding-row
| ROWS BETWEEN preceding-row AND CURRENT ROW
| ROWS BETWEEN preceding-row AND following-row
| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
| ROWS BETWEEN CURRENT ROW AND following-row
| ROWS BETWEEN following-row AND following-row
preceding-row is:
UNBOUNDED PRECEDING
| unsigned-integer PRECEDING
following-row is:
UNBOUNDED FOLLOWING
| unsigned-integer FOLLOWING
ALL
specifies whether duplicate values are included in the computation of the SUM of the
expression. The default option is ALL, which causes duplicate values to be included.
expression
specifies a numeric or interval value expression that determines the values to sum.
See “Expressions” (page 255).
inline-window-specification
specifies the window over which the SUM is computed. The
inline-window-specification can contain an optional PARTITION BY clause, an
optional ORDER BY clause and an optional window frame clause. The PARTITION BY clause
specifies how the intermediate result is partitioned and the ORDER BY clause specifies how
the rows are ordered within each partition.
window-frame-clause
specifies the window within the partition over which the SUM is computed.
Examples of SUM Window Function
• Return the running sum value of the SALARY column:
SELECT empnum, SUM (salary)
OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the running sum of the SALARY column within each department:
SELECT deptnum, empnum, SUM (salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the moving sum of the SALARY column within each department over a window of
the last 4 rows:
SUM Window Function 505