Resolving Precision Errors in Web Service SUM Functions
A SQL SUM function returns an unexpected negative result.
When performing a SUM function in a query that should always return a positive value, such as a query to determine the amount of time elapsed between an incident being opened and resolved, the query returns a negative value.
The SUM function expects operands with a different quantity of digits than was provided, leading to an incorrect result.
Cast any number passed to the SUM function to a Numeric with an explicitly-defined maximum length.
For example, if the SQL statement returning an invalid value is:
SELECT assignment_group, dv_assignment_group, SUM(TIMESTAMPDIFF(SECOND, opened_at, resolved_at)) FROM incident;
Change this statement to:
SELECT assignment_group, dv_assignment_group, SUM(CAST(TIMESTAMPDIFF(SECOND, opened_at, resolved_at) as NUMERIC(38,0))) FROM incident;