How to make rounding every 5 tenths (0.5)?

Question

How to make rounding every 5 tenths (0.5)?

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

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

The possible numbers of the fields are integers from 1 to 5, and when the average is selected, the decimals appear. But my need is for those decimals to be rounded to 0.5. 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 it returns without the correct rounding: 3,2 (3,199) and 2,3 (2,277) How do I want it to return: 3.0 and 2.5

How to do this?

mysql sql

asked by anonymous 11.06.2014 / 16:50

2 answers

1

So:

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

11.06.2014 / 18:05

Matrix in Forms How to delete texts in parentheses with regExp?

score 3 · Accepted Answer

I got a simple math account that 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'm just returning the values {1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5} as desired.