db2 => select avg(i), sum(i), avg(real(i)) from (values (1), (2) ) T
(i)

1 2 3
----------- ----------- ------------------------
1 3 +1.50000000000000E+000

1 record(s) selected.

I am expecting db2 to return (1+2) / 2 = 1.5, round up to 2, but not
1.

But it is returning 1.

Any special register need to set?

Thanks

AVG in manual "DB2 SQL Reference" was written like:
If the type of the result is integer, the fractional part of the
average is lost.

So, you may want to use a CEILING function, like this:
------------------------- Commands Entered -------------------------
SELECT avg(i) as avg_i
, sum(i) as sum_i
, CEILING( avg( real(i) ) ) as ceil_avg_i
FROM (values (1), (2) ) t(i)
;
--------------------------------------------------------------------

AVG_I SUM_I CEIL_AVG_I
----------- ----------- ------------------------
1 3 +2.00000000000000E+000

1 record(s) selected.

Or, maybe, you want to use ROUND:

SELECT avg(i) as avg_i
, sum(i) as sum_i
, ROUND( avg( real(i) ), 0 ) as ceil_avg_i
FROM (values (1), (2) ) t(i)

On 8/4/2010 6:48 AM, ChrisC wrote:
rounding modes (settable via DB CFG)


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab