For the T-SQL (SQL Server 2016) bit type, is there some way to achieve an aggregate equivalent of a logical AND and a logical OR? For example, with this table:
CREATE TABLE #Example (id int, category int, isRed bit, isBlue bit)
INSERT INTO #Example VALUES ( 1, 1, 1, 1)
INSERT INTO #Example VALUES ( 2, 1, 0, 0)
INSERT INTO #Example VALUES ( 3, 1, 1, 0)
INSERT INTO #Example VALUES ( 4, 2, 0, 1)
INSERT INTO #Example VALUES ( 5, 2, 0, 1)
INSERT INTO #Example VALUES ( 6, 2, 0, 1)
I'd like to create a query that lists, per category, if any of isRed is set (OR), and if all of isBlue is set (AND), e.g. outputting:
category anyRed allBlue
1 1 0
2 0 1
That is, I'd like something like:
SELECT
category,
OR(isRed) isAnyRed,
AND(isBlue) isAllBlue
FROM
#Example
GROUP BY
category
The only thing I could think of to try was:
SELECT
category,
MAX(isRed) isAnyRed,
MIN(isBlue) isAllBlue
FROM
#Example
GROUP BY
category
Which does not work, giving the error:
Operand data type bit is invalid for max operator.
Similar results occur with all other aggregate functions.