CODEWITHSUNDEEP
sqlpostgresql
FunnyChef
FunnyChef

Reputation: 1946

Postgresql Divide Operation is Incorrect

I'm trying to divide the following:

SELECT                                                                                                              
(count(1) filter (where status = 'Failed') / 
count(2) filter (where id > 0))
from tracking;

The data in the table looks like:

id  status
1   Failed
2   Failed  
3   Failed
4   Success

So, the result should be; .75 but instead, I get the following:

-[ RECORD 1 ]
?column? | 0

If I change from division to addition, subtraction, etc., I get the correct answer, it's just division that is incorrect for some reason.

Upvotes: 0

Views: 125

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You need a decimal point:

select (count(1) filter (where status = 'Failed') * 1.0 / 
        count(1) filter (where id > 0)
       )
from tracking;

However, I suspect that this would do what you want:

select avg( (status = 'Failed'::int) )
from tracking
where id > 0;

Upvotes: 1

Related Questions