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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

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

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.

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