Mysql count and group by when a field contains multiple values

Question

Let's have something like:

mysql> SELECT field1, field2 FROM mytable;
+--------+-------------+
| field1 | field2      |
+--------+-------------+
| tom    | a,b,c       |
| jerry  | a,c         |
| mick   | b           |
| steve  | a           |
+--------+-------------+

I'd like to have as a result the count of single values in field 2, that is:

+--------+-------------+
| value  | count       |
+--------+-------------+
| a      | 3           |
| b      | 2           |
| c      | 2           |
+--------+-------------+

This might be useful: http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql — Milen, Sep 30 '14 at 07:42

score 0 · Answer 1 · answered Sep 30 '14 at 08:18

Tyr this:

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_split`$$
CREATE PROCEDURE `proc_split`(
    inputstring VARCHAR(1000),
    delim CHAR(1)
)
BEGIN
    DECLARE strlen INT;
    DECLARE last_index INT;
    DECLARE cur_index INT;
    DECLARE cur_char VARCHAR(200);
    DECLARE len INT;
    SET cur_index=1;
    SET last_index=0;
    SET strlen=LENGTH(inputstring);  
    DROP TABLE IF EXISTS splittable;
    CREATE TEMPORARY TABLE splittable(
        id INT AUTO_INCREMENT,
        v VARCHAR(20),
        PRIMARY KEY (`ID`),
        UNIQUE KEY `ID` (`ID`)
    ) ;
    WHILE(cur_index<=strlen) DO    
    BEGIN
        IF SUBSTRING(inputstring FROM cur_index FOR 1)=delim OR cur_index=strlen THEN
            SET len=cur_index-last_index-1;
            IF cur_index=strlen THEN
               SET len=len+1;
            END IF;
            INSERT INTO splittable(`v`) VALUES (SUBSTRING(inputstring FROM   (last_index+1) FOR len));
            SET last_index=cur_index;
        END IF;
        SET cur_index=cur_index+1;
    END;
    END WHILE;
END$$
DELIMITER ;

call procedure store result to temp table,then get what you need

CALL proc_split((select group_concat(field2) from mytable),',');

SELECT *,count(1) as count FROM splittable group by v;

group_concat have a limitation of string,if you have too many records in your table,you should config this value in your mysql configuration

Mysql count and group by when a field contains multiple values

1 Answers1