How to divide COUNT(CASE ) by COUNT()

Question

I am trying to calculate a % by doing the following

(COUNT(CASE
  WHEN col1 > 0 
    THEN my_id 
  ELSE null
  END)/COUNT(my_id))*100 AS my_percent

The column, my_percent, which is output is a column of all zeros.

Individually both COUNTs return non-negative integers as expected, almost all are > 0.

COUNT(CASE
      WHEN col1 > 0 
        THEN my_id 
      ELSE null
      END) AS count_case

COUNT(my_id) AS simple_count

Why does the % function return zeros rather than positive numbers? How can I modify the code to give the expected output (positive numbers not zeros)?

Answer 1

count has a bigint return value, and PostgreSQL uses integer division that truncates fractional digits:

SELECT 7 / 3;

 ?column? 
══════════
        2
(1 row)

To avoid that, cast to double precision or numeric:

CAST(count(CASE WHEN col1 > 0 THEN my_id ELSE null END) AS double precision)
/
CAST(COUNT(my_id) AS double precision)
* 100 AS my_percent