SQL aggregate function Summing up multi column values

The SUM aggregate function is one of the best and most frequently used function in most of the SQL Queries. If you are a DB2 programmer, you need to take specialised course to know complete details about this function.

Generally the tutorials help you how to use SUM function. SUM function can be used in single or multiple columns.

Whereas this post covers not only how to use this function, and also tells about data types involved in input as well as output.

Example:

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).

EXEC SQL SELECT SUM(SALARY+COMM+BONUS)
INTO :INCOME
FROM DSN8B10.EMP;
  • The schema is SYSIBM.
  • 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.
  • The arguments can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).

The data type of the result is determined as follows:

  • DECFLOAT(34) if the argument is DECFLOAT(n)
  • Large integer if the argument is small integer.
  • 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 in SQL

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. 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

 

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.