Search code examples
mysqlsqlhammingweight

Number of set elements in column of type SET (population count)

I have a table declaration like

CREATE TABLE my_foos (
    id INT NOT NULL AUTO_INCREMENT,
    bars SET('one', 'two', 'three', 'four', 'five') NOT NULL
)

And the values

1, ('one', 'two')
2, ('two')
3, ('three', 'four', 'five')

Now I want to select id and the population count (also called Hamming weight) of bars.

In my example

SELECT id, POPCOUNT(bars)
FROM my_foos

would return

1, 2
2, 1
3, 3

What do I have to do to get the population count?

Please don't answer how to do this on the client side. I hope the solution will be usable in WHERE, ORDER BY, HAVING, etc. as well.

Solution

  • BIT_COUNT(bars) returns the number of one bits in the SET.

    I did not know/expect that you can simply use the SET datatype like an integer.