Prev: Query returns SQL1585N
Next: IBM DB2 Database Specialist - Saudi Arabia
From: Anwei Shen on 3 Aug 2010 16:59
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
From: Tonkuma on 3 Aug 2010 18:32
> Any special register need to set?
I think there is no such register variable, because a description for
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.



From: ChrisC on 3 Aug 2010 18:48
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)
From: Serge Rielau on 3 Aug 2010 19:41
On 8/4/2010 6:48 AM, ChrisC wrote:
> 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)
Or use DECFLOAT as a datatype. In decfloat you can big from a total of 6
rounding modes (settable via DB CFG)


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

 | 
Pages: 1
Prev: Query returns SQL1585N
Next: IBM DB2 Database Specialist - Saudi Arabia