dividing integers not producing decimal fractions

From: rloefgren(at)forethought(dot)net
To: pgsql-general(at)postgresql(dot)org
Subject: dividing integers not producing decimal fractions
Date: 2006-11-02 22:54:54
Message-ID: 1162508094.919806.174110@e3g2000cwe.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike G 2006-11-02 23:24:48 Cannot rename file pg_xlog\0000.....
Previous Message Magnus Hagander 2006-11-02 21:43:58 Counting records in a PL/pgsql cursor