How to output row count calculations in Oracle’s SQL*Plus

I am using SQL*Plus with Oracle 11.2 and I know how to output the count of rows adhering to some condition, e.g.:

select count(*) from MY_TABLE where MY_FIELD is not null;

Also it is easy to output all rows in MY_TABLE:

select count(*) from MY_TABLE;

However, how can I output in SQL*Plus the ratio of the two counts, i.e. how can I do in SQL*Plus a calculation on the output from SQL commands?.

---

Some notes:

1. I am interested in this because I have a long-running background process which updates the values in the MY_FIELD column over time from null to a text, so it would be nice to see quickly in SQL*Plus what ratio of rows is already done.

2. I tried

   (select count(*) from MY_TABLE where MY_FIELD is not null) / (select count(*) from MY_TABLE);
   

   but this obviously does not work because it isn’t a proper SQL command.

Answer

count(<expression>) will only count rows where <expression> isn’t NULL unless <expression> is * — then it counts any rows. So you can use:

SELECT count(my_field)
       /
       count(*)
       FROM my_table;

But if the other process filling in the values runs in only one transaction and doesn’t COMMIT every once in a while — you didn’t mention that –, you won’t see any changes until it’s done and finally COMMITs.