Perform multiple operations in a single SQL code

Question

I have a table in Mysql as follows and structure:

I would like to run an SQL query that does the following operations:

1. Total number of records (total1);
2. Count how many records have id_nps_answer between 0 and 6 (total2);
3. Count how many records have id_nps_answer between 9 and 10 (total3);
4. Perform the calculation ((total3 - total2) / total1);

The result would be made available to PHP.

Is that possible? I’m not getting the code.

Answer 1

To bring everyone on the same line, you would have to nest the subselects, so:

SELECT 
    *, 
    (total3 - total2) / total1 as calculado
FROM (
    SELECT COUNT(*) as total1 FROM nome_tabela) total1
    JOIN (SELECT COUNT(*) as total2 from nome_tabela WHERE id_nps_answer between 0 and 6) total2
    JOIN (SELECT COUNT(*) as total3 from nome_tabela where id_nps_answer between 9 and 10) total3

Answer 2

Good evening! Please let us know if the code I created worked dpois you test. thanks.

 -- 1 Totalizar o número de registros (total1);
 SELECT COUNT(id_nps_score) 
 FROM nome_da_tabela; 

 -- 2 Contar quantos registros possuem id_nps_answer entre 0 e 6 (total2);
 SELECT COUNT(id_nps_answer)
 FROM nome_da_tabela
 WHERE id_nps_answer BETWEEN 0 and 6;

 -- 3 Contar quantos registros possuem id_nps_answer entre 9 e 10 (total3);
 SELECT COUNT(id_nps_answer)
 FROM nome_da_tabela
 WHERE id_nps_answer BETWEEN 9 and 10;

 -- 4 Efetuar o cálculo ((total3 - total2) / total1);
 SELECT ((resultado3 - resultado2) / resultado1) AS '(total3-total2)/total1='
 FROM nome_da_tabela;