I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0.

select (count (refinst) / (select count(*) from patients) * 100) as "Formula" 
from patients;

The table has 15556 rows, and select count(refinst) from patients tells me that 1446 of those have a value in the refinst column. The response I'd like to get from the query would be 30.62 (1446/15556*100=30.62XXXXX, rounded to two decimals).

I'm pretty sure it has something to do with the data type of the count results (integers I'm assuming). If I divide an integer by an integer and the result is less than 0 it is truncated to 0 correct? If that's the case, can someone show me how to cast the results of the counts as a number with 2 decimal places so that the result will be rounded to 2 decimal places as well?

I'm sure there's a better way to write this code than multiple count statements. I am looking for a more processor-efficient way to write this query in particular.


3 Answers 3

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;
  • Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.

  • Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.

  • Cast to any numeric type that supports fractional digits, like @a_horse already explained.
    Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
    It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
    Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).

  • It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
    In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).

  • NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:

SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;
  • Of course you are right, the window function is not needed. But then it doesn't really make a difference (apart from readability).
    – user1822
    Jun 26, 2014 at 19:48

You need to cast each number involved in the division to a type that supports decimals:

select (count(refinst)::decimal / (select count(*) from patients)::decimal) * 100  as "Formula" 
from patients;

You might also want to try a window function instead of the scalar sub-query. It might be faster:

select (count(refinst)::decimal / (count(*) over ())::decimal) * 100 as "Formula" 
from patients;

I realize this thread is a couple of years old but: try multiplying by 100.0 rather than by 100 That should automatically cast the result as a float rather than an integer.

  • Good idea. The result is numeric, though. Even better for the task. Jul 4, 2020 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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