CODEWITHSUNDEEP
sqlsumrounding
user29
user29

Reputation: 11

issue rounding decimals in a sum SQL

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.

Upvotes: 1

Views: 4559

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

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

Upvotes: 3

Related Questions