List and count results that repeat within an array

Question

I have the following table

ID|  nome                      | Tipo
1 | Teste, João, Maria         | Indicação
2 | Teste, Maria, José         | Projeto de Lei
3 | Maria, José, João, Teste   | Indicação 
4 | Maria, João, José          | Denúncia

I needed to list the types and count the amount of each of them that has a given name, ex:

Name: Test

• Indication (2)
• Draft Law (1)
• Complaint (0)

I built the code below but it didn’t work:

SELECT   tipo,
         COUNT(tipo) AS Qtd
FROM  pessoas
WHERE FIND_IN_SET ("teste", nome)
ORDER BY COUNT(tipo) DESC

Take the example in practice: http://sqlfiddle.com/#! 9/0b41e8/1

Answer 1

I don’t know if the question as I understand it is the answer as you hope, what I did based on understanding was:

Thus:

SELECT *,COUNT(tipo) AS Qtd
FROM pessoas
WHERE FIND_IN_SET ("teste", nome)
GROUP BY tipo