Remove from account when value is 0

Question

Remove from account when value is 0

Navigation

#1 by (5 votes)
#2 by (1 votes)

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 for each type of evaluation, sum and divide to get the average. However, in the middle of these notes there may be a value of 0 (referring to "I can not tell" when filling in the form that you register in the bank), so I can not use this value in the division, as it will affect the evaluation in a wrong way.

What I would like to do is to remove the column from the split account when its value is 0, subtracting 1 from the value 12, which is the number of columns I use to average.

I thought I'd put 12 something like SELECT COUNT (*) WHERE column , but there are other columns besides those in the table.

mysql sql

asked by anonymous 11.06.2014 / 20:14

2 answers

1

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

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

...

06.07.2016 / 20:03

How to change the value of the meta tag? Pass parameter (a list object) from View (type List) to Controller. Asp.NET MVC

score 5 · Accepted Answer

Since you used zero for undefined, just add 1 to the divisor for each non-zero (or 2 for note Satisfaction, 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: IF( condicao, seVerdadeiro, seFalso) tests the condition, and returns its value. For each true, we add the weight corresponding to the divisor.