SQL - Summing number of columns which have a true boolean value for a given ID

Question

Suppose I have a table of unique transactions with transaction ID, customer ID, and a number of different boolean columns for various features of the transaction. Customers may be associated to more than one transaction.

For each distinct customer, I would like to count how many of the boolean columns have a true value.

Columns in table: transact_id, customer_id, bool_one, bool_two, bool_three...

transact_id	customer_id	bool_one	bool_two	bool_three
10001	x	True	False	False
10002	x	True	True	False
10003	y	False	False	True

Output

Customer	Number of columns
x	2
y	1

Answer 1

Consider below approach

select customer_id, 
  cast(logical_or(bool_one) as int64) 
  + cast(logical_or(bool_two) as int64) 
  + cast(logical_or(bool_three) as int64) as col_num
from your_table
group by customer_id            

if applied to sample data in your question - output is