Обсуждение: Re: Calculate a quotient for a count of boolean values (true or false)

Whoops, forgot to keep it on the list.


On 06/11/2013 11:51 AM, Alexander Farber wrote:
> Hello!
>
> In a PostgreSQL 8.4.13 why doesn't this please
> deliver a floating value (a quotient between 0 and 1):

You should upgrade to 8.4.17.

>  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
> ;

As Alban said, your math priorities were wrong to begin with.

If I understand correctly, you want "number of nice = true" divided by
"total where not null"?

select id, 1.0 * sum(case when nice then 1 end) / count(nice) as rating
from pref_rep
where nice is not null
group by id;

> more details here:
>
> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table
>

Please keep all the information on the list.