BIT_XOR_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An aggregate function to perform the equivalent of the BIT_XOR function on a data set. In other words, the resulting bits are:
-
1
at positionp
if the argument is1
at positionp
for an odd number of rows in the group. -
0
at positionp
if the argument is0
at positionp
for an even number of rows in the group.
As with most aggregate functions, NULL
values are not aggregated.
SELECT bit_xor_agg(ID), bit_xor_agg(AUTHOR_ID) FROM BOOK
create.select( bitXorAgg(BOOK.ID), bitXorAgg(BOOK.AUTHOR_ID)) .from(BOOK)
Producing:
+-------------+-------------+ | bit_xor_agg | bit_xor_agg | +-------------+-------------+ | 4 | 0 | +-------------+-------------+
Dialect support
This example using jOOQ:
bitXorAgg(BOOK.ID.coerce(TINYINT))
Translates to the following dialect specific expressions:
ASE, Redshift, SQLDataWarehouse, SQLServer
(CASE WHEN (count(CASE WHEN (BOOK.ID & 1) = 1 THEN 1 END) % 2) = 1 THEN 1 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 2) = 2 THEN 1 END) % 2) = 1 THEN 2 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 4) = 4 THEN 1 END) % 2) = 1 THEN 4 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 8) = 8 THEN 1 END) % 2) = 1 THEN 8 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 16) = 16 THEN 1 END) % 2) = 1 THEN 16 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 32) = 32 THEN 1 END) % 2) = 1 THEN 32 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & 64) = 64 THEN 1 END) % 2) = 1 THEN 64 ELSE 0 END + CASE WHEN (count(CASE WHEN (BOOK.ID & -128) = -128 THEN 1 END) % 2) = 1 THEN -128 ELSE 0 END)
Aurora MySQL, Oracle, Snowflake
bit_xor_agg(BOOK.ID)
Aurora Postgres, CockroachDB, YugabyteDB
(CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 1) = 1), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 2) = 2), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 4) = 4), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 8) = 8), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 16) = 16), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 32) = 32), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & 64) = 64), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE (BOOK.ID & -128) = -128), 2 ) = 1 THEN -128 ELSE 0 END)
BigQuery, Databricks, DuckDB, MariaDB, MySQL, Postgres, Sybase
bit_xor(BOOK.ID)
ClickHouse
groupBitXor(BOOK.ID)
DB2, Hana, Informix
(CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 1 ) = 1 THEN 1 END), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 2 ) = 2 THEN 1 END), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 4 ) = 4 THEN 1 END), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 8 ) = 8 THEN 1 END), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 16 ) = 16 THEN 1 END), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 32 ) = 32 THEN 1 END), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, 64 ) = 64 THEN 1 END), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bitand( BOOK.ID, -128 ) = -128 THEN 1 END), 2 ) = 1 THEN -128 ELSE 0 END)
Exasol
(CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 1 ) = 1 THEN 1 END), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 2 ) = 2 THEN 1 END), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 4 ) = 4 THEN 1 END), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 8 ) = 8 THEN 1 END), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 16 ) = 16 THEN 1 END), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 32 ) = 32 THEN 1 END), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, 64 ) = 64 THEN 1 END), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(CASE WHEN bit_and( BOOK.ID, -128 ) = -128 THEN 1 END), 2 ) = 1 THEN -128 ELSE 0 END)
Firebird
(CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 1 ) = 1), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 2 ) = 2), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 4 ) = 4), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 8 ) = 8), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 16 ) = 16), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 32 ) = 32), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, 64 ) = 64), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bin_and( BOOK.ID, -128 ) = -128), 2 ) = 1 THEN -128 ELSE 0 END)
H2, HSQLDB
(CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 1 ) = 1), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 2 ) = 2), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 4 ) = 4), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 8 ) = 8), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 16 ) = 16), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 32 ) = 32), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, 64 ) = 64), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitand( BOOK.ID, -128 ) = -128), 2 ) = 1 THEN -128 ELSE 0 END)
MemSQL
(CASE WHEN mod( count(CASE WHEN (BOOK.ID & 1) = 1 THEN 1 END), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 2) = 2 THEN 1 END), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 4) = 4 THEN 1 END), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 8) = 8 THEN 1 END), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 16) = 16 THEN 1 END), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 32) = 32 THEN 1 END), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & 64) = 64 THEN 1 END), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(CASE WHEN (BOOK.ID & -128) = -128 THEN 1 END), 2 ) = 1 THEN -128 ELSE 0 END)
SQLite
(CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 1) = 1) % 2) = 1 THEN 1 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 2) = 2) % 2) = 1 THEN 2 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 4) = 4) % 2) = 1 THEN 4 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 8) = 8) % 2) = 1 THEN 8 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 16) = 16) % 2) = 1 THEN 16 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 32) = 32) % 2) = 1 THEN 32 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & 64) = 64) % 2) = 1 THEN 64 ELSE 0 END + CASE WHEN (count(*) FILTER (WHERE (BOOK.ID & -128) = -128) % 2) = 1 THEN -128 ELSE 0 END)
Teradata
(CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 1 ) = 1 THEN 1 END) MOD 2) = 1 THEN 1 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 2 ) = 2 THEN 1 END) MOD 2) = 1 THEN 2 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 4 ) = 4 THEN 1 END) MOD 2) = 1 THEN 4 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 8 ) = 8 THEN 1 END) MOD 2) = 1 THEN 8 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 16 ) = 16 THEN 1 END) MOD 2) = 1 THEN 16 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 32 ) = 32 THEN 1 END) MOD 2) = 1 THEN 32 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, 64 ) = 64 THEN 1 END) MOD 2) = 1 THEN 64 ELSE 0 END + CASE WHEN (count(CASE WHEN bitand( BOOK.ID, -128 ) = -128 THEN 1 END) MOD 2) = 1 THEN -128 ELSE 0 END)
Trino
(CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 1 ) = 1), 2 ) = 1 THEN 1 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 2 ) = 2), 2 ) = 1 THEN 2 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 4 ) = 4), 2 ) = 1 THEN 4 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 8 ) = 8), 2 ) = 1 THEN 8 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 16 ) = 16), 2 ) = 1 THEN 16 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 32 ) = 32), 2 ) = 1 THEN 32 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, 64 ) = 64), 2 ) = 1 THEN 64 ELSE 0 END + CASE WHEN mod( count(*) FILTER (WHERE bitwise_and( BOOK.ID, -128 ) = -128), 2 ) = 1 THEN -128 ELSE 0 END)
Vertica
hex_to_integer(to_hex(bit_xor(hex_to_binary(to_hex(BOOK.ID)))))
Access, Derby
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!