Remove from account when value is 0

Asked

Viewed 192 times

2

I have the following query:

SELECT 
ROUND((( notaAmbiente  + 
    notaApresentacaoDasPizzas + 
    notaQualidadeDosProdutos + 
    notaVariedadeDeSabores +
    notaAtendimentoNaLoja +
    notaAtendimentoNoTel +
    notaAgilidadeNaEntrega + 
    notaCustoBeneficio +
    notaPromocoes +
    notaSite +
    notaSatisfacao + notaSatisfacao) / 12) / 0.5, 0) * 0.5 AS mediaNotaOpiniao
FROM tbOpiniao

In this query I take the notes of each type of evaluation, sum and divide to get the average. However, in the middle of these notes there can be a value of 0 (referring to "I don’t know how to say" when filling out the form that I register with the bank), so I can’t use this value in the division, because it will affect the evaluation in the wrong way.

What I’d like to do is remove the column from the division account when its value is 0, subtracting 1 from 12, which is the amount of columns I use to average.

I thought of putting in place of 12 something of the type (SELECT COUNT(*) WHERE column <> 0), but there are other columns besides those of select in the table.

mysql sql

2 answers

5


Since you used zero for undefined, just add 1 to the divisor for each non-zero (or 2 for noteSatisfaction, which has weight 2 in your example):

SELECT 
ROUND ( ( (
   notaAmbiente  + 
   notaApresentacaoDasPizzas + 
   notaQualidadeDosProdutos + 
   notaVariedadeDeSabores +
   notaAtendimentoNaLoja +
   notaAtendimentoNoTel +
   notaAgilidadeNaEntrega + 
   notaCustoBeneficio +
   notaPromocoes +
   notaSite +
   notaSatisfacao * 2
) / (
   IF( notaAmbiente, 1, 0 ) +
   IF( notaApresentacaoDasPizzas, 1, 0 ) +
   IF( notaQualidadeDosProdutos, 1, 0 ) +
   IF( notaVariedadeDeSabores, 1, 0 ) +
   IF( notaAtendimentoNaLoja, 1, 0 ) +
   IF( notaAtendimentoNoTel, 1, 0 ) +
   IF( notaAgilidadeNaEntrega, 1, 0 ) +
   IF( notaCustoBeneficio, 1, 0 ) +
   IF( notaPromocoes, 1, 0 ) +
   IF( notaSite, 1, 0 ) +
   IF( notaSatisfacao, 2, 0 )
) ) / 0.5, 0 ) * 0.5 AS mediaNotaOpiniao
FROM tbOpiniao

Explanation: THE IF( condicao, seVerdadeiro, seFalso) tests the condition, and returns the respective value. For each true, we add the weight corresponding to the divisor.

1

Since you used zero for undefined, just add 1 to the divisor for each non-zero

IF( notaAmbiente, 1, 0 ) +
   IF( notaApresentacaoDasPizzas, 1, 0 ) +
   IF( notaQualidadeDosProdutos, 1, 0 ) +
   IF( notaVariedadeDeSabores, 1, 0 ) +

...

Browser other questions tagged mysql sql

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