Aggregate bitwise-OR in a subquery with partitioning?

Question

I've been looking at how to do a bitwise-OR aggregation in TSQL in a subquery, and the answers given in a popular question don't seem to maintain partitioning. Is there any way for me to partition?

Let's say we have the following data:

id | someCount | someFlags
1  | 2         | 0
1  | 3         | 2
1  | 0         | 1
2  | 1         | 4
2  | 5         | 0
2  | 1         | 1

I can get the SUM of the someCount field partitioned by id without losing any of the original rows like so:

SELECT [testSum] = SUM([someCount]) OVER (PARTITION BY [id]) FROM myTable

Is there a way to do this with a bitwise OR operation? so I'd get:

id | value
1  | 3
1  | 3
1  | 3
2  | 5
2  | 5
2  | 5

score 2 · Accepted Answer · answered Jun 08 '21 at 17:21

One way would be as below. Extend it out to the number of bit positions that you care about.

Having to repeat the PARTITION BY [id] is a bit tedious but I would expect SQL Server to only do the partitioning work once and calculate all the MAX expressions inside the same aggregate operator.

SELECT *, 
        MAX(someFlags & 1) OVER (PARTITION BY [id])  +
        MAX(someFlags & 2) OVER (PARTITION BY [id])  +
        MAX(someFlags & 4) OVER (PARTITION BY [id])  +
        MAX(someFlags & 8) OVER (PARTITION BY [id])
FROM (VALUES
(1 , 2 , 0),
(1 , 3 , 2),
(1 , 0 , 1),
(2 , 1 , 4),
(2 , 5 , 0),
(2 , 1 , 1)
)V(id ,someCount ,someFlags)

Andy3B · Answer 2 · 2021-06-08T17:15:08.493

0

Try this:

 DECLARE @MyTable TABLE( ID int, SomeCount int, SomeFlags int)
INSERT INTO @MyTable
    VALUES
            (1, 2, 0)
        ,   (1, 3, 2)
        ,   (1, 0, 1)
        ,   (2, 1, 4)
        ,   (2, 5, 0)
        ,   (2, 1, 1)
;

WITH SumTable   AS 
    (
        SELECT 
                iD
            ,   TestSum     =   SUM(SomeCount)
            ,   BitOrSum    =   SUM(SomeCount | SomeFlags)
        FROM @MyTable
        GROUP BY ID
    )

SELECT  
        T.ID
    ,   S.TestSum
    ,   S.BitOrSum
FROM    @MyTable    T
JOIN    SumTable    S   ON  S.ID = T.ID
ORDER BY ID

edited Jun 08 '21 at 17:15

answered Jun 08 '21 at 17:07

Andy3B

444
2
6

This returns `6` and `11` for `BitOrSum` - not the correct `3` and `5` – Martin Smith Jun 08 '21 at 17:32
That's correct. For ID=1: 2 or 0=2; 3 or 2 = 3; 0 or 1=1. SUM(2,3,1)=6 – Andy3B Jun 08 '21 at 18:00
Perhaps you just want the TestSum, which is a simple SUM of SomeCount. This is the only field that gives you the desired result, but there is no Bitwise-OR involved/needed in that calculation. – Andy3B Jun 08 '21 at 18:14
The desired results are from bitwise or ing all the values in the partition. I.e. select 0|2|1, 4|0|1 – Martin Smith Jun 08 '21 at 18:20

Aggregate bitwise-OR in a subquery *with partitioning*?

2 Answers2

Aggregate bitwise-OR in a subquery with partitioning?