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.