try_sum
aggregate function
Applies to: Databricks SQL
Databricks Runtime 11.3 LTS and above
Returns the sum calculated from values of a group, or NULL if there is an overflow.
Syntax
try_sum ( [ALL | DISTINCT] expr ) [FILTER ( WHERE cond ) ]
This function can also be invoked as a window function using the OVER
clause.
Arguments
expr
: An expression that evaluates to a numeric or interval.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)
.
If expr
is an interval the result type matches expr
.
Otherwise, a DOUBLE.
If DISTINCT
is specified only unique values are summed up.
If the result overflows the result type Databricks SQL returns NULL. To return an error instead use sum.
Examples
> SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
30
> SELECT try_sum(col) FILTER(WHERE col <15)
FROM VALUES (5), (10), (15) AS tab(col);
15
> SELECT try_sum(DISTINCT col) FROM VALUES (5), (10), (10), (15) AS tab(col);
30
> SELECT try_sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
25
> SELECT try_sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
NULL
-- try_sum overflows a BIGINT
> SELECT try_sum(c1) FROM VALUES(5E18::BIGINT), (5E18::BIGINT) AS tab(c1);
NULL
-- In ANSI mode sum returns an error if it overflows BIGINT
> SELECT sum(c1) FROM VALUES(5E18::BIGINT), (5E18::BIGINT) AS tab(c1);
ERROR
-- try_sum overflows an INTERVAL
> SELECT try_sum(c1) FROM VALUES(INTERVAL '100000000' YEARS), (INTERVAL '100000000' YEARS) AS tab(c1);
NULL
-- sum returns an error on INTERVAL overflow
> SELECT sum(c1) FROM VALUES(INTERVAL '100000000' YEARS), (INTERVAL '100000000' YEARS) AS tab(c1);
Error: ARITHMETIC_OVERFLOW
Related
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru