尝试将 sql 查询的结果舍入为 2 位小数

Question

在下面的查询中，我尝试为 SUM 行返回带有 2 位小数 (.00( 的数据：

SELECT
CONVERT(varchar, YEAR(COALESCE(release_date, requested_date)))
+ RIGHT('00' + CONVERT(varchar, MONTH(COALESCE(release_date, requested_date))), 2) AS yrmnth,
salesrep,
customer_name,
SUM(price_per_ea * COALESCE(open_release_qty, open_order_qty)) AS ext_price

Answer 1

您可以使用转换来控制格式

SELECT
CONVERT(VARCHAR, YEAR(COALESCE(release_date, requested_date))) 
+ RIGHT('00' + CONVERT(VARCHAR, 
MONTH(COALESCE(release_date, requested_date))),2) as yrmnth
,salesrep
,customer_name
,  Convert(decimal(12,2), 
SUM(price_per_ea * COALESCE(open_release_qty, open_order_qty)))  as ext_price 

Answer 2

在选择中使用 round 函数：

round(SUM(price_per_ea * COALESCE(open_release_qty, open_order_qty)),2)