How to eliminate decimal places from a built-in sql function?-CodePudding

I have the following SQL Server query:

select max(AveragePrice) as [LARGEST AVERAGE]
from
    (select b.brand_id, round(avg(prod_price),2) as AveragePrice
    from lgbrand b, lgproduct p
    where b.brand_id = p.brand_id
    group by b.brand_id) x

Output

LARGEST AVERAGE
22.590000

How do I trim the decimals to only show "22.59"?

CodePudding user response：

Use ROUND to round up or down to specified number of decimal places.

select round(max(AveragePrice),2) as [LARGEST AVERAGE]
from
    (select b.brand_id, round(avg(prod_price),2) as AveragePrice
    from lgbrand b, lgproduct p
    where b.brand_id = p.brand_id
    group by b.brand_id) x

CodePudding user response：

You can use the function FORMAT for that:

SELECT FORMAT(MAX(averageprice),'#0.00') AS largest_average,...

CodePudding user response：

you can try below methods to convert to 2 decimal places.

declare @table table(val decimal(8,6))
insert into @table 
values (22.590000)

select Format(val,'##.##') from @table
SELECT cast(val as decimal(4,2)) from @table
SELECT convert(decimal(4,2), val) from @table

22.59