BIT_AND_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An aggregate function to perform the equivalent of the BIT_AND function on a data set. In other words, the resulting bits are:
-
1
at positionp
if the argument is1
at positionp
for every row in the group. -
0
at positionp
if the argument is0
at positionp
for at least one row in the group.
As with most aggregate functions, NULL
values are not aggregated.
SELECT bit_and_agg(ID), bit_and_agg(AUTHOR_ID) FROM BOOK
create.select( bitAndAgg(BOOK.ID), bitAndAgg(BOOK.AUTHOR_ID)) .from(BOOK)
Producing:
+-------------+-------------+ | bit_and_agg | bit_and_agg | +-------------+-------------+ | 0 | 0 | +-------------+-------------+
Dialect support
This example using jOOQ:
bitAndAgg(BOOK.ID.coerce(TINYINT))
Translates to the following dialect specific expressions:
-- ASE, MEMSQL, SQLDATAWAREHOUSE, SQLITE, SQLSERVER (CASE min(CASE (BOOK.ID & 1) WHEN 0 THEN 0 WHEN 1 THEN 1 END) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 2) WHEN 0 THEN 0 WHEN 2 THEN 2 END) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 4) WHEN 0 THEN 0 WHEN 4 THEN 4 END) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 8) WHEN 0 THEN 0 WHEN 8 THEN 8 END) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 16) WHEN 0 THEN 0 WHEN 16 THEN 16 END) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 32) WHEN 0 THEN 0 WHEN 32 THEN 32 END) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & 64) WHEN 0 THEN 0 WHEN 64 THEN 64 END) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE min(CASE (BOOK.ID & -128) WHEN 0 THEN 0 WHEN -128 THEN -128 END) WHEN -128 THEN -128 WHEN 0 THEN 0 END) -- AURORA_MYSQL, AURORA_POSTGRES, H2, ORACLE, SNOWFLAKE bit_and_agg(BOOK.ID) -- BIGQUERY, COCKROACHDB, DUCKDB, MARIADB, MYSQL, POSTGRES, REDSHIFT, SYBASE, YUGABYTEDB bit_and(BOOK.ID) -- DB2, HANA, HSQLDB, INFORMIX, TERADATA (CASE min(CASE bitand( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE min(CASE bitand( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END) WHEN -128 THEN -128 WHEN 0 THEN 0 END) -- EXASOL (CASE min(CASE bit_and( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE min(CASE bit_and( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END) WHEN -128 THEN -128 WHEN 0 THEN 0 END) -- FIREBIRD (CASE min(CASE bin_and( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE min(CASE bin_and( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END) WHEN -128 THEN -128 WHEN 0 THEN 0 END) -- TRINO bitwise_and_agg(BOOK.ID) -- VERTICA hex_to_integer(to_hex(bit_and(hex_to_binary(to_hex(BOOK.ID))))) -- ACCESS, DERBY /* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!