SQL/Snowflake How to count across columns?

user_17867165 0
Viewed 386

I am trying to count the number of columns in a row that are true. For example:

INDEX COL 1 COL 2 COL 3 # TRUE
1 true false true 2
2 false false false 0
3 true true true 3

I know I could solve this using a case statement with a pascal's triangle number of conditions, but is there an easier or more scalable way to do this?

snowflake-cloud-data-platform sql
5 Answers

Explicit conversion Boolean -> INT:

SELECT *, col1::INT + col2::INT + col3::INT AS num_of_true
FROM tab
user_50708790

The CASE of Samuel's answer CASE WHEN [COL 1]=1 THEN 1 ELSE 0 END can be replace with an IFF IFF("COL 1",1,0)

thus

SELECT 
    *,
    IFF("COL 1",1,0) + IFF("COL 2",1,0) + IFF("COL 3",1,0) AS "# TRUE"
FROM table

You can loop across columns in a stored procedure, or use client side scripts in python/javascript/etc to build the SQL.

user_439920

I don't believe there's a way to automatically iterate through columns in SQL (in a sort of 'SUM columns 1-5' sort of way), so I believe you'd have to explicitly name each column.

You could create your calculated column above by doing something like

SELECT *,
(
CASE WHEN [COL 1]=1 THEN 1 ELSE 0 END +
CASE WHEN [COL 2]=1 THEN 1 ELSE 0 END +
CASE WHEN [COL 3]=1 THEN 1 ELSE 0 END
) AS "# TRUE"
FROM [...]
user_160330210

A solution that doesn't care about how many columns you have - by leveraging object_construct:

with data as (
    select $1 id, $2 c1, $3 c2, $4 c3, $5 c4
    from values(1, true, false, true, true)
    , (2, true, true, false, false)
    , (3, true, true, true, true)
), transformed as (
    select object_construct(a.*) b
    from data a
)

select seq, count_if(value='true')
from transformed, table(flatten(b))
group by 1

enter image description here

user_1324380

This might come in more handy if you have a lot of non-null columns.

select regexp_count(concat(col1,col2,col3),'true', 1, 'i') -- i is a case insensitive flag
user_99471590
Related