sum aggregate function (Databricks SQL)

Returns the sum calculated from values of a group.

Syntax

sum ( [ALL | DISTINCT] expr ) [FILTER ( WHERE cond ) ]

Arguments

  • expr: An expression that evaluates to a numeric.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

If expr is an integral number type, a BIGINT. If expr is DECIMAL(p, s) the result is DECIMAL(p + min(10, 31-p), s). Otherwise, a DOUBLE.

If DISTINCT is specified only unique values are summed up.

Examples

> SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
 30
> SELECT sum(col) FILTER(WHERE col <15)
    FROM VALUES (5), (10), (15) AS tab(col);
 15
> SELECT sum(DISTINCT col) FROM VALUES (5), (10), (10), (15) AS tab(col);
 30
> SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
 25
> SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
 NULL