Logical Aggregations

BITWISE_AND_AGG

BITWISE_AND_AGG(x) Returns the bitwise AND of all input values in 2's complement representation.

WITH examples AS (
      SELECT 1 AS x       -- 00001
      UNION ALL SELECT 2  -- 00010
      UNION ALL SELECT 4  -- 00100
      UNION ALL SELECT 8  -- 01000
      UNION ALL SELECT 16 -- 10000
  )
  SELECT BITWISE_AND_AGG(x)
  FROM examples

BITWISE_OR_AGG

BITWISE_OR_AGG(x) Returns the bitwise OR of all input values in 2's complement representation.

WITH examples AS (
      SELECT 1 AS x       -- 00001
      UNION ALL SELECT 2  -- 00010
      UNION ALL SELECT 4  -- 00100
      UNION ALL SELECT 8  -- 01000
      UNION ALL SELECT 16 -- 10000
  )
  SELECT BITWISE_OR_AGG(x)
  FROM examples

BOOL_AND

BOOL_AND(x) Returns true if every value in the input is true, false otherwise. Return value and all arguments are boolean.

WITH examples AS (
      SELECT true AS x
      UNION ALL SELECT true
      UNION ALL SELECT true
  )
  SELECT BOOL_AND(x)
  FROM examples

true

WITH examples AS (
      SELECT true AS x
      UNION ALL SELECT true
      UNION ALL SELECT false
  )
  SELECT BOOL_AND(x)
  FROM examples

false

BOOL_OR

BOOL_OR(x) Returns true if one value in the input is true, false otherwise. Return value and all arguments are boolean.

WITH examples AS (
      SELECT false AS x
      UNION ALL SELECT false
      UNION ALL SELECT false
  )
  SELECT BOOL_OR(x)
  FROM examples

false

WITH examples AS (
      SELECT false AS x
      UNION ALL SELECT false
      UNION ALL SELECT true
  )
  SELECT BOOL_OR(x)
  FROM examples

true

COUNT_IF

COUNT_IF(x) Returns the number of elements in x which are true.

WITH examples AS (
      SELECT false AS x
      UNION ALL SELECT false
      UNION ALL SELECT true
  )
  SELECT COUNT_IF(x)
  FROM examples

EVERY

EVERY(x) An alias of BOOL_AND function.