Question 196735 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search