SUM

Calculates the sum of the values in expr. If all values are NULL, NULL is returned.

Syntax

Aggregate Function

SUM([DISTINCT] expr)

Window Function

SUM([DISTINCT] expr) OVER (
                            [PARTITION BY part]
                            [ORDER BY ord [ASC|DESC] [window_frame]]
                            )

Arguments

expr: Numeric value (INTEGER or DECIMAL). If the value is not numeric, an attempt is made to convert the value to a numeric value. If this is not possible, an error is returned.

---

part (optional): Expression by which to partition. See also Working with window functions.

---

ord (optional): Expression by which to sort within each partition. See also Working with window functions.

---

window_frame (optional): Defines the subset of rows within the window for which the summed values are to be returned. See also Working with window functions.

Return Value

The function returns the same data type as the numeric data type of expr.

Usage Notes

• If expr is not numeric, expr is implicitly converted to a floating-point value. If the conversion is not possible, an error is returned.
• When DISTINCT is used, ORDER BY and window_frame cannot be used.

Example 1 | With 'DISTINCT' Clause

select  sum(col1), 
        sum(col2),
        sum(DISTINCT col1), 
        sum(DISTINCT col2) 
             from  (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, NULL as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+-----------+-----------+--------------------+--------------------+
| SUM(COL1) | SUM(COL2) | SUM(DISTINCT COL1) | SUM(DISTINCT COL2) |
+-----------+-----------+--------------------+--------------------+
| 360       | 160       | 210                | 140                |
+-----------+-----------+--------------------+--------------------+

Example 2 | With 'GROUP BY' Clause

select  grp1,
        sum(col1) as result1,
        sum(col2) as result2
            from  (select 'a' as ord1, 'g1' as grp1, 10 as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, 40 as col1, NULL as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, 60 as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, 50 as col1, 50 as col2)
        GROUP BY grp1
        ORDER BY grp1;

+-----------+-----------+--------------------+
|   GRP1    | RESULT1   | RESULT2            |
+-----------+-----------+--------------------+
| g1        | 50        | 50                 |
| g2        | 100       | NULL               |
| g3        | 210       | 110                |
+-----------+-----------+--------------------+

Example 3 | Window Function

select  grp1,
        col1,
        sum(col1) OVER(PARTITION BY grp1) as result1,
        sum(col1) OVER(PARTITION BY grp1 ORDER BY ord1 ASC) as result2,
        sum(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC) as result3,
        sum(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as result3_moved_s
             from  (select 'a' as ord1, 'g1' as grp1, 10 as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, 40 as col1, NULL as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, 60 as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, 50 as col1, 50 as col2);

+------+------+---------+---------+---------+-----------------+
| GRP1 | COL1 | RESULT1 | RESULT2 | RESULT3 | RESULT3_MOVED_S |
+------+------+---------+---------+---------+-----------------+
| g1   | 10   | 50      | 10      | 50      | 30              |
| g1   | 20   | 50      | 30      | 40      | 50              |
| g1   | 20   | 50      | 50      | 20      | 40              |
| g2   | 30   | 100     | 30      | 100     | 60              |
| g2   | 30   | 100     | 60      | 70      | 100             |
| g2   | 40   | 100     | 100     | 40      | 70              |
| g3   | 50   | 210     | 50      | 210     | 100             |
| g3   | 50   | 210     | 100     | 160     | 160             |
| g3   | 60   | 210     | 160     | 110     | 160             |
| g3   | 50   | 210     | 210     | 50      | 110             |
+------+------+---------+---------+---------+-----------------+