Try it now and let us know what you think. Switch to the new look >>
You can return to the original look by selecting English in the language selector above.
BitWise Aggregate Functions
Topics
Amazon Redshift supports the following bitwise aggregate functions:

BIT_AND

BIT_OR

BOOL_AND

BOOL_OR
BIT_AND and BIT_OR
The BIT_AND and BIT_OR functions run bitwise AND and OR operations on all of the values in a single integer column or expression. These functions aggregate each bit of each binary value that corresponds to each integer value in the expression.
The BIT_AND function returns a result of 0
if none of the bits is set to
1 across all of the values. If one or more bits is set to 1 across all values, the
function returns an integer value. This integer is the number that corresponds to
the
binary value for the those bits.
For example, a table contains four integer values in a column: 3, 7, 10, and 22. These integers are represented in binary form as follows:
Integer  Binary value 

3  11 
7  111 
10  1010 
22  10110 
A BIT_AND operation on this dataset finds that all bits are set to 1
in
the secondtolast position only. The result is a binary value of 00000010
,
which represents the integer value 2
; therefore, the BIT_AND function
returns 2
.
If you apply the BIT_OR function to the same set of integer values, the operation
looks for any value in which a 1
is found in each
position. In this case, a 1
exists in the last five positions for at least
one of the values, yielding a binary result of 00011111
; therefore, the
function returns 31
(or 16 + 8 + 4 + 2 + 1
).
BOOL_AND and BOOL_OR
The BOOL_AND and BOOL_OR functions operate on a single Boolean or integer column or
expression. These functions apply similar logic to the BIT_AND and BIT_OR functions.
For
these functions, the return type is a Boolean value (true
or
false
):

If all values in a set are true, the BOOL_AND function returns
true
(t
). If any value is false, the function returnsfalse
(f
). 
If any value in a set is
true
, the BOOL_OR function returnstrue
(t
). If no value in a set istrue
, the function returnsfalse
(f
).
NULLs in BitWise Aggregations
When a bitwise function is applied to a column that is nullable, any NULL values are eliminated before the function result is calculated. If no rows qualify for aggregation, the bitwise function returns NULL. The same behavior applies to regular aggregate functions. For example:
select sum(venueseats), bit_and(venueseats) from venue where venueseats is null; sum  bit_and + null  null (1 row)
DISTINCT Support for BitWise Aggregations
Like other aggregate functions, bitwise functions support the DISTINCT keyword. However, using DISTINCT with these functions has no impact on the results. The first instance of a value is sufficient to satisfy bitwise AND or OR operations, and it makes no difference if duplicate values are present in the expression being evaluated. Because the DISTINCT processing is likely to incur some query execution overhead, do not use DISTINCT with these functions.