issue rounding decimals in a sum SQL

Question

I have a problem with a query, the rounding only works in the first row, do you know why?

SELECT DISTINCT a.articulo AS articulo
    ,b.NombreArticulo AS nom_art
    ,sum(a.uni_vta_caja) AS Unidades
    ,round(sum(a.imp_vta_caja_neto), 2) AS Venta
    ,substring(cast(a.fec_vta AS VARCHAR), 4, 2) AS mes
FROM ConsultaDWH.dbo.F_Venta_21606 a
INNER JOIN consultadwh.dbo.articulos b ON a.articulo = b.articulo
WHERE a.Articulo IN (
        '1031260'
        ,'1031209'
        )
GROUP BY a.articulo
    ,b.NombreArticulo
    ,substring(cast(a.fec_vta AS VARCHAR), 4, 2)

The result appears:

articulo  nom_art                                   Unidades  Venta                 mes
1031209   Enutre Supl Alim Botellin 237ml Vainilla  686.0     14137.73              06
1031260   Enutre Supl Alim Botellin 237ml Fresa     597.0     12329.799999999999    06

Look at the .73 and .9999999 etc.

What DBMS are you using? What is the datatype of `imp_vta_caja_neto`? Can you provide sample data? — Siyual, Jul 12 '16 at 14:56
It's hard to tell which column is which in your result. Please add headers — Jonny, Jul 12 '16 at 14:59
Try casting `Venta` as a Decimal(x,2). You're probably running into the Float precision issue. — Tab Alleman, Jul 12 '16 at 15:04
http://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples — Tab Alleman, Jul 12 '16 at 15:08

Aaron Dietz · Answer 1 · 2016-07-12T15:18:07.480

3

If you want 2 decimal places, you will need to use CAST instead of ROUND:

CAST(SUM(a.imp_vta_caja_neto) AS DECIMAL (8,2))

ROUND can be misleading because most of us naturally think ROUND(123.123,1) should output one decimal place, 123.1 What it actually does is round to the precision of the first decimal place, 123.100. If you want an explicit number of decimal places in your output, using CAST as decimal to will achieve this. CAST(123.123 as decimal (8,1)) = 123.1

edited Jul 12 '16 at 15:18

answered Jul 12 '16 at 15:05

Aaron Dietz

10,137
1
14
26

This doesn't answer the actual question, which is "Do you know why?" – Tab Alleman Jul 12 '16 at 15:06
Thanks a lot for the feedback, it worked properly. But as Tab said, what was the reason for that result?. – user29 Jul 12 '16 at 15:09
@user29 Always happy to help - I had to take care of something at work but I've added some explanation now. – Aaron Dietz Jul 12 '16 at 15:22

issue rounding decimals in a sum SQL

1 Answers1