Re: Calculate a quotient for a count of boolean values (true or false) - Mailing list 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