| From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
|---|---|
| To: | "Alexander Farber *EXTERN*" <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Calculate a quotient for a count of boolean values (true or false) |
| Date: | 2013-06-11 10:57:10 |
| Message-ID: | A737B7A37273E048B164557ADEF4A58B17BB1E37@ntex2010a.host.magwien.gv.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Alexander Farber wrote:
> In a PostgreSQL 8.4.13 why doesn't this please
> deliver a floating value (a quotient between 0 and 1):
>
> select
> id,
> count(nullif(nice, false)) - count(nullif(nice, true)) /
> count(nice) as rating
> from pref_rep where nice is not null
> group by id
> ;
> id | rating
> -------------------------+--------
> DE10072 | -1
> DE10086 | 18
> DE10087 | 1
> DE10088 | -1
> DE10095 | 276
> DE10097 | 37
> DE10105 | 5
Because you are using integer division.
Cast one of the values to double precision, like this:
(count(nullif(nice, false)) - count(nullif(nice, true))) / count(nice)::float8
Yours,
Laurenz Albe
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Luca Ferrari | 2013-06-11 10:57:32 | Re: databse version |
| Previous Message | Alexander Farber | 2013-06-11 10:33:13 | Re: Calculate a quotient for a count of boolean values (true or false) |