SQL – Rounding a math expression

November 8, 2021

I am trying to get a value rounded to two decimal places. However the results in the following code is still displaying 4 decimal places

select p.ProductName, p.UnitPrice, s.CompanyName, c.CategoryName,
 case 
    when c.CategoryName in ('Condiments', 'Beverages') then round((UnitPrice + (UnitPrice * .2)), 2)
    when c.CategoryName in ('meat/poultry', 'seafood') then round((UnitPrice + (UnitPrice * .15)), 2)
    when s.CompanyName = 'New Orleans Cajun Delights' then round((UnitPrice + (UnitPrice * .08)), 2)
    else p.unitprice
 end as NewUnitPrice
from products p
join Categories c on c.CategoryID = p.CategoryID
join Suppliers s on s.SupplierID = p.SupplierID

>Solution :

The solution is to change the data type to 2 points of precision. See the example below.

SELECT
    CAST(2.49532785 AS NUMERIC(32,8)) AS [Original]    --Original 8 precision numeric
    ,ROUND(CAST(2.49532785 AS NUMERIC(32,8)),2)  AS [Rounded]    --Rounded 8 precision numeric
    ,CAST(2.49532785 AS NUMERIC(32,2))  AS [Cast]    --Original 2 precision numeric