Thread: dividing integers not producing decimal fractions

I'm trying to produce a decimal fraction by dividing integer fields
like this fragment of the query:

...((cntoh0.count + cntoh1.count)/ttl_deptcat.ttlcount) as "Ratio"...

I get this output

  dept_cat  | cnt_oh_0 | cnt_oh_1 | sum_oh_0+1 | ttl_count | Ratio
------------+----------+----------+------------+-----------+-------
 101500     |      116 |       18 |    134 |   238 |     0
 101800     |      409 |       46 |    455 |   467 |     0
 101900     |      197 |        1 |    198|   198 |     1

Why does "Ratio" display as 0, rather than the proper decimal fraction?
All of the *oh* fields are integers and ttl_count is a bigint. I've
tried wrapping all the integer fields with round(<fieldname>, 3) and I
do get the proper number of zeros to the right of the decimal on the
*oh* fields but not on the "Ratio" field. How do I get fractions when
dividing integers? Cast as something? (I suppose this is going to be
obvious, after-the-fact.)

Thanks for any tips,

r

On Nov 2, 2006, at 23:54 , rloefgren@forethought.net wrote:

> I'm trying to produce a decimal fraction by dividing integer fields
> like this fragment of the query:
>
> ...((cntoh0.count + cntoh1.count)/ttl_deptcat.ttlcount) as "Ratio"...
...
> How do I get fractions when dividing integers? Cast as something?

Exactly. Just as in most computer languages, incidentally:

# select 1 / 2 as x;
x
---
0
(1 row)

# select 1 / 2::float as x;
   x
-----
0.5
(1 row)

Alexander.

Alexander Staubo wrote:
> On Nov 2, 2006, at 23:54 , rloefgren@forethought.net wrote:
>
> > I'm trying to produce a decimal fraction by dividing integer fields
> > like this fragment of the query:
> >
> > ...((cntoh0.count + cntoh1.count)/ttl_deptcat.ttlcount) as "Ratio"...
> ...
> > How do I get fractions when dividing integers? Cast as something?
>
> Exactly. Just as in most computer languages, incidentally:
>
> # select 1 / 2 as x;
> x
> ---
> 0
> (1 row)
>
> # select 1 / 2::float as x;
>    x
> -----
> 0.5
> (1 row)
>
> Alexander.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

You're right (I dug around in the documentation and edjoocated myself).
However:

sales=# select 1/2;
 ?column?
----------
        0
(1 row)

and:

sales=# select 1/2::float;
 ?column?
----------
      0.5
(1 row)

but:

sales=# select 1/2*4::float;
 ?column?
----------
        0
(1 row)

or:

sales=# select (1/2)*4::float;
 ?column?
----------
        0
(1 row)

or try:

sales=# select (1/2)* 4::numeric(6,3);
 ?column?
----------
    0.000
(1 row)

I'll just avoid this next time by not throwing "integer" around so
quickly. (At least until I become something of a postgres obi-wan...)

thanks,

r

On Fri, Nov 03, 2006 at 02:03:59PM -0800, rloefgren@forethought.net wrote:
> You're right (I dug around in the documentation and edjoocated myself).
> However:

<snip>

> sales=# select 1/2::float;
>  ?column?
> ----------
>       0.5
> (1 row)

Note that in this case the "float" cast only applies to the last
number. That's why you get this:

> sales=# select (1/2)*4::float;
>  ?column?
> ----------
>         0
> (1 row)

The integer divide happens first. It is best to apply the cast to the
first element of the expression, as expressions are parsed
left-to-right, so:

select (1::float/2)::4;

Works better. However, mostly it's better to explicitly make all your
constants non-integer if that's what you mean. This statement:

select (1.0/2.0)*4.0;

Gives the same result, but doesn't need any casts.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.