CODEWITHSUNDEEP
mysqlsqlaggregate-functions
ftkg
ftkg

Reputation: 1762

How to best perform grouping in a "bit field"?

Borrowing from this question, let's say there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this:

  file_id  |  fsize  |  permissions
-----------+---------+---------------
        1  |  200    |  6    ( <-- 6 = 4 + 2 = read + write)
        2  |  300    |  4    ( <-- 4 = 4 = read)
        3  |  400    |  3    ( <-- 3 = 2 + 1 = write + execute)
        4  |  500    |  1    ( <-- 1 = execute)

Considering this field can hold multiple flags, I need to return a result grouped by these three flags, something like this: 

 |  fsize  |  permissions
 +---------+---------------
 |  900    |  1    
 |  600    |  2    
 |  500    |  4

What is the best way to do this?

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use a join for this:

select p.permission, sum(fsize)
from t join
     (select 1 as permission union all
      select 2 union all
      select 4
     ) p
     on p.permission & t.permissions > 0
group by p.permission
order by p.permission;

Upvotes: 2

Related Questions