How do I select a variable which gives an output the same as the excel function:
COUNTIFS(A1:D1,"<25", A1:D1, ">16")?
You can do this with
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;
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;