CODEWITHSUNDEEP
sqldb2
max092012
max092012

Reputation: 407

divide 2 columns in DB2

How to divide 2 columns in DB2. For example I have the below query 

select x.planned,y.unplanned,(x.planned /y.unplanned) average from 
 ((select count(1) planned from 
  (select 1 from table 
  --------
  union all
  select 1 from table
  --------)) x
  join
 (select count(1) unplanned from 
 (select 1 from table
  --------
  union all
  select 1 from table
  --------)) y
  on 1=1)

This query brings output as 

PLANNED  UNPLANNED  AVERAGE
  25         6         4

The value in AVERAGE is incorrect it should display 4.16 instead of 4. I'm not sure what is the mistake.

Upvotes: 1

Views: 1833

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

25 / 6 equals 4⅙ it does not equal 4.16 unless you are explicitly rounding down the result to two decimal places.

For the most flexibility and precision when using decimal numbers in Db2 you could consider using use DECFLOAT. For example

$ db2 "values 25::decfloat/6"

1                                         
------------------------------------------
       4.166666666666666666666666666666667

When rounding DEFFLOAT values, Db2 uses the values of the CURRENT DECFLOAT ROUNDING MODE special register to determine the rounding mode. This defaults to ROUND_HALF_EVEN (and is the only values allowed in some Db2 configurations).

db2 "values ROUND(25::decfloat/6,2)"

1                                         
------------------------------------------
                                      4.17

  1 record(s) selected.

to truncate (i.e. round down) then you can use DECIMAL and FLOATs as Mao suggested, or to avoid issues with binary floating points, just DECIMALs

$ db2 "values DECIMAL(25::DECIMAL/6,7,2)"

1        
---------
     4.16

  1 record(s) selected.

Upvotes: 1

mao
mao

Reputation: 12267

You can use something like: CAST( float(x.planned)/float(y.unplanned) AS DECIMAL(9,2) ) as average to explicitly control the result precision and scale

Upvotes: 1

Related Questions