CODEWITHSUNDEEP
mysqlsqlgroup-bybit
Irfy
Irfy

Reputation: 9607

SQL group by bit set?

Is there any way of grouping the results of a select by individual bits set in a column? That is, ignoring combinations of various bits (otherwise a simple group by column would suffice).

For example, assuming a table has the values 1 through 10 for the group-by column exactly once (presented below with binary representation to simplify the construction/verification of the following group-by statement result):

 1 0001
 2 0010
 3 0011
 4 0100
 5 0101
 6 0110
 7 0111
 8 1000
 9 1001
10 1010

the group-by effect I want to achieve would look like:

select <bit>, count(*), group_concat(<column>) from <table> group by <...>

0    5    1,3,5,7,9
1    5    2,3,6,7,10
2    4    4,5,6,7
3    3    8,9,10

assuming the first bit is "bit 0".

I'm using MySQL at the moment, so ideally a solution should work there; but I'd be interested in other RDBMS solutions, if any.

Upvotes: 4

Views: 395

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You would need to split the values up and then reaggregate. Something like this:

select n.n, group_concat(col1)
from t join
     (select 0 as n union all select 1 union all select 2 union all select 3
     ) n
     on (1 << n) & bits > 0
group by n;

Upvotes: 2

Related Questions