counting overlapping bitwise columns in mysql

I have a table in which there is a bitwise column representing a list of statuses that can be attached to an entry. Each entry can have multiple statuses selected (hence the use of the bitwise logic).

What I'd like to do is pull a query that will tell me how many entires there are for each status (i.e. how many times each bit is turned on). The difficulty I have is that there is of course overlap so a GROUP BY or a DISTINCT is not going to cut it (as far as I can see).

As an example let's just have two values, 1 and 2. and the following data

Id | Status
1 | 1
2 | 1
3 | 2
4 | 3

Now, I want to count how many entries there are for each bit so I'd like something that counts that 3 value into both the 1 and 2 totals, outputting something li开发者_如何转开发ke this:

Bit | Count
1 | 3
2 | 2

The closest I can get so far seems to be pulling out the distinct values and then adding those with multiple entries into their corresponding counts using PHP. Obviously, I'd like to do something a bit more elegant.

Any ideas?

---

Expand the bits table as required

select `bit`, count(*) `count`
from bitt s
inner join (select 1 `bit` union all
            select 2 union all
            select 3 union all
            select 4 union all
            select 5) bits on s.status & Pow(2,bits.`bit`-1)
group by bits.`bit`

---

You could do:

SUM(IF(`Status`&1,1,0)) as `count1`,
SUM(IF(`Status`&2,1,0)) as `count2`,
SUM(IF(`Status`&4,1,0)) as `count4`

If you want to optimize it, you can still GROUP BY Status, but then you would need (a little) post-processing to sum the 8 rows you would get for a 3-bit situation.

---

One more variant -

SELECT 0, COUNT(IF(status >> 0 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 1, COUNT(IF(status >> 1 & 1 = 1, 1, NULL)) FROM table1
UNION
SELECT 2, COUNT(IF(status >> 2 & 1 = 1, 1, NULL)) FROM table1
...