# Determine percentage from count() without cast issues

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.

``````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). – a_horse_with_no_name Jun 26 '14 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. – Erwin Brandstetter Jul 4 at 2:09