www.mimer.com/developer

Results of Set Functions

---

When the argument of a set function is a numerical value, the precision and scale of the set function result is evaluated in accordance with the rules given below. If the argument is an expression, the expression is first evaluated as described in Expressions before the set function is applied.

Evaluation of Set Functions

	FLOAT(p')	INTEGER(p')	DECIMAL(p', s')
SUM	FLOAT(p)1	INTEGER(p)2	DECIMAL(p, s)3
AVG	FLOAT(p)1	INTEGER(p)4	DECIMAL(p, s)5
MAX, MIN	FLOAT(p)4	INTEGER(p)4	DECIMAL(p, s)6
COUNT	INTEGER(10)	INTEGER(10)	INTEGER(10)

1 p=max(15, p') 2 p=min(45, 10+p') 3 p=min(45, 10+p') s=s' 4 p=p' 5 p=min(45, 10+p') s=p-(p'-s') 6 p=p' s=s'

Some examples of SUM and AVG follow:

 SUM(INTEGER(3)) gives INTEGER(13)
 
 SUM(INTEGER(12)) gives INTEGER(22)
 
 SUM(DECIMAL(38,10)) gives DECIMAL(45,10)
 
 SUM(DECIMAL(4,2)) gives DECIMAL(14,2)
 
 AVG(INTEGER(3)) gives INTEGER(3)
 
 AVG(INTEGER(12)) gives INTEGER(12)
 
 AVG(DECIMAL(38,10)) gives DECIMAL(45,17)
 
 AVG(DECIMAL(4,2)) gives DECIMAL(14,12)
 

Note: Often, the average of a series of integers is required as a decimal rather than an integer.
This may be achieved by casting the value to a decimal using the CAST function.
For example, if the values in the integer column COL are 1, 3 and 6, then AVG(COL) returns 3 but AVG(CAST(COL as decimal(5,4))) returns 3.3333.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se