How to aggregate boolean column

Simply do a GROUP BY, use MAX() to return T if available, else F.

select col_1, max(col_2), max(col_3), max(col_4)
from tablename
group by col_1

If COL_2 to COL_4 are text columns (char, varchar, varchar2, nvarchar, nvarchar2) containing 'T' or 'F', then you can just take the MAX of them, since 'T' > 'F', i.e. 'T' comes after 'F' in lexical order.

SELECT COL_1, MAX(COL_2) AS COL_2, MAX(COL_3) AS COL_3, MAX(COL_4) AS COL_4
FROM table
GROUP BY COL_1

Explanation: The locical operation OR returns TRUE, if at least one of the operands is TRUE. MAX() returns "T" if at least one value is "T" and otherwise "F".


Note: If the Boolean columns were declared as

COL_x NUMBER(1) DEFAULT 0 NOT NULL

or any other numeric type then I would take MAX(ABS(col_x)), since a negative value counts as TRUE as well. (If you have an Access frontend with a ComboBox attached to a Boolean, it yields the values 0 or -1.)


Just as a side note (doesn't work with Oracle): In PostgreSQL, you would do this:

SELECT col_1, bool_or(col_2), bool_or(col_3), bool_or(col_4)
FROM tablename GROUP BY col_1 ORDER BY col_1

Tags:

Sql

Oracle