SQL - Rounding a math expression

Question

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

Simplify (you shouldn't need 3 tables to show us your rounding problem) - [mcve]. — jarlh, Nov 08 '21 at 20:41
`else p.unitprice` is probably the culprit. Change it to `else ROUND(p.unitprice,2)`. — kmoser, Nov 08 '21 at 20:42
@JamesKrueger Your example shows NewUnitPrice as six decimal places, not four. Can you clarify the problem? — kmoser, Nov 08 '21 at 20:47
The new unit price should be 2 decimal places 21.60 instead of 21.600000 — James Krueger, Nov 08 '21 at 20:49
How can it matter how many trailing zeros there are? The number is still the same, the zeros is just a display function which you handle in your front end. — Dale K, Nov 08 '21 at 20:53
And also, [here](https://stackoverflow.com/questions/10380197/rounding-off-to-two-decimal-places-in-sql) is another similar question. — devlin carnate, Nov 08 '21 at 20:53
@DaleK because I only want 2 decimals. It should not be difficult to achieve. — James Krueger, Nov 08 '21 at 20:54
@JamesKrueger Which specific solution(s) did you try and what was the output? — kmoser, Nov 08 '21 at 20:54
@JamesKrueger - you need to edit your question and explain **how** that duplicate question doesn't resolve your issue. — devlin carnate, Nov 08 '21 at 20:54

score 0 · Accepted Answer · answered Nov 08 '21 at 20:52

0

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

answered Nov 08 '21 at 20:52

Andrew O'Brien

1,793
1
12
24

3

It would be better to mark this question as a duplicate instead of answering it. There are so many questions like this on SO. We don't need to answer yet another one.... – devlin carnate Nov 08 '21 at 20:55

SQL - Rounding a math expression

1 Answers1