20/03/2017

[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.

No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.