dividing integers not producing decimal fractions

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/