Postgres Pro
Downloads
Home   >   mailing lists

Thread: Calculate a quotient for a count of boolean values (true or false)

Calculate a quotient for a count of boolean values (true or false)

From
Alexander Farber
Date:
Hello!

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

I am trying to calculate a sum of ratings
given by users to each other
to draw a pie chart in a game, more details here:

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

Thank you

Re: Calculate a quotient for a count of boolean values (true or false)

From
Alban Hertroys
Date:
Because you're calculating a - (b/c) instead of (a-b)/c


On 11 June 2013 11:51, Alexander Farber <alexander.farber@gmail.com> wrote:
Hello!

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

I am trying to calculate a sum of ratings
given by users to each other
to draw a pie chart in a game, more details here:

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

Thank you


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: Calculate a quotient for a count of boolean values (true or false)

From
Alexander Farber
Date:
Hello,

On Tue, Jun 11, 2013 at 11:59 AM, Alban Hertroys <haramrae@gmail.com> wrote:
> Because you're calculating a - (b/c) instead of (a-b)/c

> On 11 June 2013 11:51, Alexander Farber <alexander.farber@gmail.com> wrote:
>> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table

right, sorry! But now I have -1,0,1 only:

 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
-------------------------+--------
 DE10019                 |      0
 DE10030                 |      1
 DE10047                 |      0
 DE10049                 |      1
 DE10051                 |      0
 DE10059                 |     -1
 DE10062                 |      0
 DE10067                 |     -1
 DE10072                 |     -1


Regards
Alex

Re: Calculate a quotient for a count of boolean values (true or false)

From
Albe Laurenz
Date:
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