I have the following sample table:
ID_USER | FRUTAS ----------------------------- 1 | Laranja;Banana;Maçã 2 | Abacaxi;Laranja;Uva 3 | Pera;Laranja;Banana;Melão
It would be possible to perform a select count with order by which would bring the following result:
FRUTA | COUNT --------------------------- Abacaxi | 1 Banana | 2 Laranja | 3 Maçã | 1 Melão | 1 Pera | 1 Uva | 1
This query will return what you are looking for:
SELECT fruta, SUM(total) as count
FROM(
SELECT count(*) AS total, REPLACE(REPLACE(REPLACE(x.fruta,'?',''),'.',''),'!','') as fruta
FROM(
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.frutas, ';', n.n), ';', -1) fruta
FROM tabela t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.frutas) - LENGTH(REPLACE(t.frutas, ';', '')))
ORDER BY fruta
) AS x
GROUP BY x.fruta
) AS y
GROUP BY fruta
See working in SQLFiddle
I solved the following procedure, I created a procedure where, I insert the fruits into a temporary table and at the end I make a simple query in this table counting the frutas , the result is expected. consider that the name of my table is frutas and the field name also in case you need to change change the procedure and run in your database.
DROP PROCEDURE IF EXISTS sp_get_count_frutas;
DELIMITER |
CREATE PROCEDURE sp_get_count_frutas()
BEGIN
DECLARE qtd_posicoes INT;
DECLARE end_cursor INT DEFAULT 0;
DECLARE qtd_aux INT DEFAULT 1;
DECLARE str_frutas VARCHAR(255);
DECLARE cur_frutas CURSOR FOR SELECT
frutas
FROM frutas;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_cursor = 1;
DROP TABLE IF EXISTS tmp_table_frutas;
CREATE TEMPORARY TABLE tmp_table_frutas(fruta VARCHAR(255));
OPEN cur_frutas;
REPEAT
FETCH cur_frutas INTO str_frutas;
IF NOT end_cursor THEN
SET qtd_posicoes = (SELECT LENGTH(str_frutas) - LENGTH(REPLACE(str_frutas,';',''))) + 1;
WHILE qtd_posicoes >= qtd_aux DO
INSERT INTO tmp_table_frutas(fruta) VALUE(REPLACE(SUBSTRING(SUBSTRING_INDEX(str_frutas, ';', qtd_aux),
LENGTH(SUBSTRING_INDEX(str_frutas, ';', qtd_aux-1)) + 1),
';', ''));
SET qtd_aux = qtd_aux + 1;
END WHILE;
SET qtd_aux = 1;
END IF;
UNTIL end_cursor END REPEAT;
CLOSE cur_frutas;
SELECT
COUNT(*) AS total_fruta,
fruta
FROM tmp_table_frutas
GROUP BY fruta;
END
|
DELIMITER ;
CALL sp_get_count_frutas();