Categories:

Aggregate Functions (General) , Window Functions (General, Window Frame)

SUM

Returns the sum of non-NULL records for expr. You can use the DISTINCT keyword to compute the sum of unique non-null values. If all records inside a group are NULL, the function returns NULL.

See also:

COUNT , MIN / MAX

Syntax

Aggregate function

SUM( [ DISTINCT ] <expr1> )

Window function

SUM( [ DISTINCT ] <expr1> ) OVER (
                                 [ PARTITION BY <expr2> ]
                                 [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ]
                                 )

For details about window_frame syntax, see Window Frame Syntax and Usage.

Arguments

expr1

This is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).

expr2

This is the optional expression to partition by.

expr3

This is the optional expression to order by within each partition. (This does not control the order of the entire query output.)

Usage Notes

  • Numeric values are summed into an equivalent or larger data type.

  • When passed a VARCHAR expression, this function implicitly casts the input to floating point values. If the cast cannot be performed, an error is returned.

  • When this function is called as a window function (i.e. with an OVER clause):

    • If the OVER clause contains an ORDER BY subclause, then:

      • A window frame is required. If no window frame is specified explicitly, then the ORDER BY implies a cumulative window frame:

        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

        For information about window frames, including syntax and examples, see Window Frame Syntax and Usage.

        For information about implied window frames, see also Window Frame Usage Notes.

      • Using the keyword DISTINCT inside the window function is prohibited and results in a compile-time error.

Examples

CREATE OR REPLACE TABLE sum_example(k INT, d DECIMAL(10,5),
                                    s1 VARCHAR(10), s2 VARCHAR(10));

INSERT INTO sum_example VALUES
(1, 1.1, '1.1','one'), (1, 10, '10','ten'),
(2, 2.2, '2.2','two'), (2, null, null,'null'),
(3, null, null, 'null'),
(null, 9, '9.9','nine');

SELECT * FROM sum_example;

+------+----------+------+------+
|    K |        D | S1   | S2   |
|------+----------+------+------|
|    1 |  1.10000 | 1.1  | one  |
|    1 | 10.00000 | 10.0 | ten  |
|    2 |  2.20000 | 2.2  | two  |
|    2 |     NULL | NULL | null |
|    3 |     NULL | NULL | null |
| NULL |  9.00000 | 9.9  | nine |
+------+----------+------+------+

SELECT SUM(d), SUM(s1) FROM sum_example;

+----------+---------+
|   SUM(D) | SUM(S1) |
|----------+---------|
| 22.30000 |    23.2 |
+----------+---------+

select k, SUM(d), SUM(s1) FROM sum_example GROUP BY k;

+------+----------+---------+
|    K |   SUM(D) | SUM(S1) |
|------+----------+---------|
|    1 | 11.10000 |    11.1 |
|    2 |  2.20000 |     2.2 |
|    3 |     NULL |    NULL |
| NULL |  9.00000 |     9.9 |
+------+----------+---------+

SELECT SUM(s2) FROM sum_example;

100038 (22018): Numeric value 'one' is not recognized

The script below shows the use of this function (and some other window functions) in a windowing context:

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
⇧
Back to top