CODEWITHSUNDEEP
sqloracle-databasecountif
amsbam1
amsbam1

Reputation: 119

How to do countIf() in Oracle

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.

Upvotes: 6

Views: 52637

Answers (3)

Mr. Mak
Mr. Mak

Reputation: 867

Think, you have a 'user_table', where some of the user's 'status' are active (code-11) and others are inactive (code-22). You can count active and inactive with this sql below.

select count(case when status = 11 then 1 end) u_active, count(case when status = 22 then 1 end) u_inactive, from user_table;

Upvotes: 0

user5683823
user5683823

Reputation:

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;

Upvotes: 9

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You would do this in a SQL query using case:

select sum(case when col between 16 and 25 then 1 else 0 end)
from t;

Note that between is inclusive in SQL, not exclusive, so based on your code logic:

select sum(case when col > 16 and col < 25 then 1 else 0 end)
from t;

Upvotes: 4

Related Questions