CODEWITHSUNDEEP
sqlgoogle-bigquerybooleanaggregate
mpollinger
mpollinger

Reputation: 63

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

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

Upvotes: 0

Views: 1462

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

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

enter image description here

Upvotes: 1

Related Questions