The way the data is stored in the database makes this kind of query very difficult because the data is not standardized .
The code below performs your query, but it is necessary to know in advance how many options can be chosen, since you will need to create a CASE WHEN
for each of them.
Considering that the alternatives are the numbers of 0
a 12
SELECT
SUM(CASE
WHEN Pressaus LIKE "0,%" THEN 1
WHEN Pressaus LIKE "%,0" THEN 1
WHEN Pressaus LIKE "%,0,%" THEN 1
WHEN Pressaus = "0" THEN 1
ELSE 0
END) AS "Total Opcao0",
SUM(CASE
WHEN Pressaus LIKE "1,%" THEN 1
WHEN Pressaus LIKE "%,1" THEN 1
WHEN Pressaus LIKE "%,1,%" THEN 1
WHEN Pressaus = "1" THEN 1
ELSE 0
END) AS "Total Opcao1",
SUM(CASE
WHEN Pressaus LIKE "2,%" THEN 1
WHEN Pressaus LIKE "%,2" THEN 1
WHEN Pressaus LIKE "%,2,%" THEN 1
WHEN Pressaus = "2" THEN 1
ELSE 0
END) AS "Total Opcao2",
.
. -- CASE WHEN para cada opcao...
.
SUM(CASE
WHEN Pressaus LIKE "12,%" THEN 1
WHEN Pressaus LIKE "%,12" THEN 1
WHEN Pressaus LIKE "%,12,%" THEN 1
WHEN Pressaus = "12" THEN 1
ELSE 0
END) AS "Total Opcao12"
FROM jud_Processos;
Basically each CASE WHEN
creates a column for each option and in that column the number 1
is inserted if the line has the option in the Pressaus
field. Since the option can be in the beginning, in the middle, in the end or exactly the field, we need 4 clauses WHEN
one for each possibility.
Then the function SUM()
adds up how many times each alternative has appeared.
Example in sqlfiddle: link