Percentage of each value within a table field. The field stores two values, 1 for yes and 2 for No

Question

I’m trying to develop a customer satisfaction survey application where the user answers some questions with numbers 1 to 10 and another yes or no.

In one of the reports, I show the average of each field of the bank, but in the fields of yes or no question, I’m saving 1 for yes and 2 for no.

I am doubtful if it is possible to solve only with the database, returning only the percentage of each field response.

SELECT COUNT(p.id_pesquisa)"pesquisas", 
FORMAT(AVG(p.atendimento),2)"atendimento", 
FORMAT(AVG(p.espera),2)"espera", 
FORMAT(AVG(p.ambiente),2)"ambiente", 
FORMAT(AVG(p.qualidade_produtos),2)"qualidade_produtos", 
FORMAT(AVG((p.atendimento + p.espera + p.ambiente + p.qualidade_produtos)/4),2)'media'
FROM empresa e 
INNER JOIN campanha c ON (e.id_empresa = c.id_empresa)
INNER JOIN pesquisa p ON (c.id_campanha = p.id_campanha)
WHERE c.id_empresa = 4 

Answer 1

Something in this sense may solve your problem, but I would indicate the use of a view in the database to facilitate the search of the data of this report.

SELECT  (SELECT SUM(u.atendimento = 'S') FROM pesquisa u) / (SELECT COUNT(u.id_pesquisa) FROM pesquisa u) AS satisfacaoAtendimento

To help with the filter issue, you can use the outer select data in the inside Where, as follows

  SELECT  (SELECT SUM(u.atendimento = 'S') FROM pesquisa u WHERE p.id_pesquisa = u.id_pesquisa) /
    (SELECT COUNT(u.id_pesquisa) FROM pesquisa u  WHERE p.id_pesquisa = u.id_pesquisa) AS satisfacaoAtendimento
FROM empresa e
INNER JOIN campanha c ON (e.id_empresa = c.id_empresa)
INNER JOIN pesquisa p ON (c.id_campanha = p.id_campanha)
WHERE c.id_empresa = 4