Snowflake: How do I multiple a column of binaries (cast as integers)?

Question

Lets say I have table with 1 column like this:

Col A
1
0
0
1

If I SUM it, then I will get this:

Col A
2

My question is: how do I multiply Col A so I get the following?

Col A 0

I found a similar answer but not one that works for a list of 1s and 0s because it's using the log() function:

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable

Perhaps use a case expression. If the column contains a zero, then zero, else the solution from that other answer. — Isolated, Jun 29 '23 at 13:18

score 0 · Answer 1 · answered Jun 29 '23 at 13:27

0

Assuming that the A column really only contains 0 and 1, then we can simply check for the presence of at least one 0. If found, the product is 0, otherwise it is 1.

SELECT CASE WHEN COUNT(CASE WHEN [Col A] = 0 THEN 1 END) > 0 THEN 0 ELSE 1 END AS product
FROM yourTable;

answered Jun 29 '23 at 13:27

Tim Biegeleisen

502,043
27
286
360

If it really is only 0 or 1, the product is equal to the min? – MatBailie Jun 29 '23 at 13:34

score 0 · Answer 2 · answered Jun 29 '23 at 13:56

If allowed values are only 0 and 1 then BITAND_AGG could be used:

Returns the bitwise AND value of all non-NULL numeric records in a group.

For each bit position, if all rows have the bit set to 1, then the bit is set to 1 in the result. If any rows have that bit set to zero, the result is zero.

SELECT BITAND_AGG(colA)
FROM tab;

Snowflake: How do I multiple a column of binaries (cast as integers)?

2 Answers2