How to do countIf() in Oracle

Question

How do I select a variable which gives an output the same as the excel function:

COUNTIFS(A1:D1,"<25", A1:D1, ">16")?

I.e. to count the number of times the values in my four fields are between 16 and 25.

Answer 1

You can do this with count() and case expressions. Note: case has an optional else clause; if it is not used, then the default else "value" is null. Count only counts non-null values, so you can combine these observations to write compact code. WHAT is counted doesn't matter; so the case expression may return a number (1 is usual, but 0 is just as valid and will give the same result, since the values are COUNTED, not SUMMED) - but you could also have a string like 'x' or a date in the case expression. Just to illustrate that, I will use a string.

select count( case when col > 16 and col < 25 then 'x' end ) as ct from your_table;

Here your_table is the name of your table, col is the name of the column containing the values, and ct is the name of the resulting column (the label for the count of values that satisfy your condition).

Of course, in a database you can get the same result more easily:

select count(*) as ct from your_table where col > 16 and col < 25;

Note, though, that the values are in one column.

If instead you have a table with four COLUMNS and many rows, and all the values in all columns are numbers, and you want to add a column showing how many values are strictly between 16 and 25 IN EACH ROW, the solution is different (but it uses the same ideas):

select col1, col2, col3, col4, 
       case when col1 > 16 and col1 < 25 then 1 else 0 end +
       case when col2 > 16 and col2 < 25 then 1 else 0 end +
       case when col3 > 16 and col3 < 25 then 1 else 0 end +
       case when col4 > 16 and col4 < 25 then 1 else 0 end    as ct
from   my_table;