Perform multiple operations in a single SQL code

Asked

Viewed 48 times

0

I have a table in Mysql as follows and structure:

inserir a descrição da imagem aqui

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.

2 answers

3


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

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;
  • It worked, but I need the scripts to be all nested nested in a single code, that’s my question.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.