CODEWITHSUNDEEP
sqlsql-servert-sqlaggregatesql-server-2016
Jason C
Jason C

Reputation: 40315

Aggregate logical AND / OR for columns with type 'bit'

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.

Upvotes: 5

Views: 3049

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use subquery and find as below:

Select Category, AnyRed = Case When SmRed > 0 then 1 else 0 End,
        AllBlue = Case When SmBlue = Cnt then 1 else 0 End from  (
    select category, SMRed = sum(iif(isred=1,1,0)), SMBlue = Sum(iif(isBlue=1,1,0)), Cnt= Count(id) from #Example
    group by category
) a

Upvotes: 2

Backs
Backs

Reputation: 24903

MIN and MAX functions can be used:

SELECT
  category,
  MAX(CONVERT(tinyint,isRed)) isAnyRed,
  MIN(CONVERT(tinyint,isBlue)) isAllBlue
FROM
  #Example
GROUP BY
  category

But you have to convert bit to some numeric value (tinyint), as MIN and MAX work only with numbers, not booleans.

Upvotes: 10

Related Questions