-4

My query result should be 0.000534, but I'm getting 0.

Please help me.

psql -Upostgres -dcans -c \
    'select (select COUNT(*)
             FROM brd.gaf_info
             where can_piecesre_sucbattery = 'pr' 
               and gaf_start_date > '2019-11-13 01:01:01.754'
            ) /
            (select COUNT(*)
             FROM brd.gaf_info
             where gaf_start_date > '2019-11-13 01:01:01.754'
            );'
  • 2
    Your expression is integer / integer, so the result is integer. You must CAST your COUNTs to some floating-point datatype before divide. – Akina Nov 14 at 4:35
  • 1
    @LaurenzAlbe I mean in general. The specific datatype is unimportant. – Akina Nov 14 at 7:05
3

You are using integer division (more precisely bigint) which yields not fractional values. You need to cast one value to numeric in order to get what you want:

select (select COUNT(*)
         FROM brd.gaf_info
         where can_piecesre_sucbattery = 'pr' 
           and gaf_start_date > '2019-11-13 01:01:01.754'
        )::numeric /
        (select COUNT(*)
         FROM brd.gaf_info
         where gaf_start_date > '2019-11-13 01:01:01.754'
        );

Additionally, please note that with the help of a FILTER clause you can calculate the result without subqueries, since the same table is used in both subqueries, and one of the conditions is also the same. You just need to move the other condition to the FILTER clause, like this:

SELECT COUNT(*) FILTER (WHERE can_piecesre_sucbattery = 'pr')::numeric
     / COUNT(*)
FROM brd.gaf_info
WHERE gaf_start_date > '2019-11-13 01:01:01.754';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.