1

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?

Community
  • 1
  • 1
ftkg
  • 1,652
  • 4
  • 21
  • 33

1 Answers1

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786