How to make rounding every 5 tenths (0.5)?

Asked

Viewed 1,680 times

3

I have a select that returns the average of the columns. I used Round to remove a few decimal places and round the numbers.

The possible numbers of the fields are integers from 1 to 5, and when doing the average select the decimals appear. But my need is for these decimals to be rounded to 0.5 intervals. Example: 1.0, 1.5, 2.0, 2.5, 3.0 etc

My select is this:

SELECT 
nomNome,
ROUND(((notaAmbiente + 
notaApresentacaoDasPizzas + 
    notaQualidadeDosProdutos + 
    notaVariedadeDeSabores +
    notaAtendimentoNaLoja +
    notaAtendimentoNoTel +
    notaAgilidadeNaEntrega + 
    notaCustoBeneficio +
    notaPromocoes +
    notaSite +
    notaSatisfacao + notaSatisfacao) / 12), 1) AS notaOpiniao
FROM tbOpiniao

As returns without the correct rounding: 3,2 (3,199) and 2,3 (2,277) How do I want you to return: 3.0 and 2.5

How to do this?

mysql sql

  • Give an example of output! type output a value has to stay that value ?

  • 3,1999 would not be 3,5 ?

  • not because it is below the middle half 0.1999 < 0.25. From 0.25 to 0.49 round to 0.5

  • So it’s not 0.5 to 0.5? look at your question?

  • I made an SQL, which in my opinion is your reality, including tested.

2 answers

3


I got with a simple math account what I wanted:

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

Now I am only returning the values {1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5} as desired.

  • That’s right ?

  • Yes, tested and working!!

  • I prefer the expression ) * 2, 0) / 2, but it still works.

1

Thus:

SELECT id, valor, decimalvalue,
       (CASE WHEN ((decimalvalue) < .25)
                  THEN TRUNCATE(valor,0)
             WHEN ((decimalvalue) > .25 AND (decimalvalue) < .49)
                  THEN TRUNCATE(valor,0) + .5
             WHEN ((decimalvalue) > .5 AND (decimalvalue) < .75)
                  THEN ((valor - decimalvalue) + .5)
             WHEN (decimalvalue > .75) 
                  THEN (1 - decimalvalue + valor)
             ELSE (valor)
       END) as newvalor
FROM (
SELECT id, 
       valor, 
       valor - TRUNCATE(valor,0) as decimalvalue        
FROM new_table) AS NEWTABLE

Example: Sqlfiddle

Browser other questions tagged mysql sql

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