CODEWITHSUNDEEP
sqlsql-servergroup-byaggregatelogical-operators
Chris Dane
Chris Dane

Reputation: 13

t-SQL Aggregate Logic

Lets say we have a table with an ID and a bit column. We execute a query that returns any number of rows (for this example with ID>5). What I want to do is to have an AND aggregate operation across the results of the bit column. I have figured out a workaround, but it's not pretty:

SELECT 
CASE WHEN COUNT(ID) = SUM(CAST(isTrue AS INT)) THEN 1 ELSE 0 END AS areTrue
FROM Table
WHERE ID > 5

What I'm doing here, since there is no aggregate function to multiply the results, is practically comparing the total number of records with the sum of the integer representation of the bit column. If they're equal, there are no 'falses', so it works as an AND operation between them.

An OR would be to get the MAX from the column, which is pretty straightforward.

The only problem is that this is particularly ugly and should be killed with fire. Is there any elegant, proper, way to do this?

Upvotes: 1

Views: 781

Answers (2)

Petio Ivanov
Petio Ivanov

Reputation: 305

You could achieve this by using a variable to store the calculation, for example:

Declare @result bit = 1

SELECT @result = @result & isTrue 
FROM Table
WHERE ID > 5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If you want to do logic on the bits, how about:

select max(cast(isTrue as int)) as AnyTrue,
       min(cast(isTrue as int)) as AllTrue,
       max(1 - cast(isTrue as int)) as AnyFalse,
       min(1 - cast(isTrue as int)) as AllFalse

Upvotes: 3

Related Questions