previous : next

Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17

BIT_XNOR_AGG

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

An aggregate function to perform the equivalent of the BIT_XNOR function on a data set. In other words, the resulting bits are:

  • 0 at position p if the argument is 1 at position p for an odd number of rows in the group.
  • 1 at position p if the argument is 0 at position p for an even number of rows in the group.

As with most aggregate functions, NULL values are not aggregated. 

SELECT bit_xnor_agg(ID), bit_xnor_agg(AUTHOR_ID) FROM BOOK
create.select( bitXNorAgg(BOOK.ID), bitXNorAgg(BOOK.AUTHOR_ID)) .from(BOOK)

Producing: 

+--------------+--------------+ | bit_xnor_agg | bit_xnor_agg | +--------------+--------------+ | -5 | -1 | +--------------+--------------+

Dialect support

This example using jOOQ:

bitXNorAgg(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

~(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, Spanner, Sybase

~(bit_xor(BOOK.ID))

ClickHouse

bitNot(groupBitXor(BOOK.ID))

DB2, Hana, Informix

bitnot((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

bit_not((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

bin_not((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

bit_xnor_agg(BOOK.ID)

HSQLDB

((0 - (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)) - 1)

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))

Oracle

((0 - bit_xor_agg(BOOK.ID)) - 1)

Snowflake

bitnot(bit_xor_agg(BOOK.ID))

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

bitnot((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

bitwise_not((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

/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo