[SQL] Excel COUNTIFS - count columns matching criteria in a row

Excel in his arsenal of useful functions has COUNTIFS, basically a count of how many elements in a one dimensional range match a specific criteria. It says multidimensional, but it's not, it's either the same criteria twice for more dimensions or a different criteria. Key point: one list at a time.

However this is a very basic need which is not immediately achievable in SQL as well since we cannot loop over columns in a row. That is, unless we remember that PIVOTing is actually a thing. In this specific case we use the inverse operation, UNPIVOT.

Assume you have this table (Oracle DB syntax):

CREATE TABLE test( id NUMBER

,col1 NUMBER

,col2 NUMBER

,col3 NUMBER

);

And put some data into it:

INSERT ALL

INTO test (id, col1, col2, col3) VALUES (1, 0.2, 0.3, 1)

INTO test (id, col1, col2, col3) VALUES (2, 3, 4, 1)

INTO test (id, col1, col2, col3) VALUES (3, 0, 1, 0)

SELECT 1 FROM DUAL;

Now you can simulate COUNTIFS as:

SELECT id

      ,SUM(CASE WHEN val = 0 THEN 1 ELSE 0 END) AS cnt

FROM test

UNPIVOT(val FOR col_name IN (col1, col2, col3)) u

GROUP BY id

;

This will give you as output a row for each id with the count of items = 0. Of course the condition can be extended.

Basically we alter our table to look like this:

ID, COL

id1, col1

id1, col2

id1, col3

id2, col1

id2, col2

id2, col3

.

.

.

Then the counting operation appears obvious.