SUM Aggregate function ideas to use on multi columns

The SUM aggregate function is very frequently used function in SQL. If you are a DB2 programmer, you need to take specialized SQL course to know more on SUM function.

This post tells you how to use “SQL SUM function”. The SUM function can be used in single or multiple columns. Also, tells about data types involved in input as well as output.

• SUM SQL Function in DB2 SQL

Set the large integer host variable INCOME to the total income from all sources (salaries, commissions, and bonuses) of the employees represented in the sample table DSN8B10.EMP.

If DEC31 is not in effect, the resultant sum is DECIMAL(15,2) because all three columns are DECIMAL(9,2).

Sample Query on SUM function

EXEC SQL SELECT SUM(SALARY+COMM+BONUS)
INTO :INCOME
FROM SYSIBM.DSN8B10.EMP;

• Top Rules to Use SUM Function

1. The schema is SYSIBM.
2. The argument values can be of any built-in numeric data type, and their sum must be within the range of the data type of the result.
3. The arguments can also be a character string or graphic string data type.
4. The string input is implicitly cast to a numeric value of DECFLOAT(34).

• How Data type Casting Happens in Output Result

1. DECFLOAT(34) if the argument is DECFLOAT(n)
2. Large integer if the argument is small integer.
3. Double precision floating-point if the argument is single precision floating-point. Otherwise, the result is the same as the data type of the argument.

Related: DB2 SQL Video Tutorial -if you need more information

• SUM Function Result can be NULL

The result can be null.

If the data type of the argument values is decimal, the scale of the result is the same as the scale of the argument values, and the precision of the result depends on the precision of the argument values and the decimal precision option:

If the precision of the argument values is greater than 15 or the DEC31 option is in effect, the precision of the result is min(31,P+10), where P is the precision of the argument values. Otherwise, the precision of the result is 15.

The function is applied to the set of values derived from the argument values by the elimination of null values.

Also Read: NULL Values in SQL top Rules

• What happens when DISTINCT is given

If DISTINCT is specified, redundant duplicate values are also eliminated.

If the function is applied to an empty set, the result is the null value. Otherwise, the result is the sum of the values in the set.

Ref: IBM.com