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

Question

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.}

Answer 1

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.

• SQLFiddle demo

Answer 2

I think there are many ways to solve this particular problem but the approach I am using is this,

CHAR_LENGTH(bars) - CHAR_LENGTH(REPLACE(bars, ',', '')) + 1

• SQLFiddle Demo

If you want, you manke that as a Function

DELIMITER $$
CREATE FUNCTION POPCOUNT(popList SET('one', 'two', 'three', 'four', 'five'))
RETURNS INT 
BEGIN
    RETURN CHAR_LENGTH(popList) - CHAR_LENGTH(REPLACE(popList, ',', '')) + 1;
END $$
DELIMITER ;

and you can call it like,

SELECT POPCOUNT(bars) FROM my_foos