SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

3 rows affected

drop table if exists dbo.res
  
select *, qty * price as total
  , cast(sum(qty * price) over ()  as decimal(18,2)) as sum1
  , sum(cast(qty * price as decimal(18,2))) over () as sum2
  , cast(sum(qty * price) over ()  as decimal(18,2)) / cast(sum(qty) over()as decimal(18,2)) as sum3
  , cast(sum(cast(qty * price as decimal(18,2))) over() as decimal(18,2)) / cast(sum(qty) over ()as decimal(18,2)) as sum4
  from dbo.test

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(N'select qty*price from dbo.test', null, null)
  
select
  sum(qty * price) total
  , sum(qty) qty_all
  , sum(cast(qty as decimal(24,2)) *cast(price as decimal(24,4))) total_with_cast_before_mul
  , sum(cast(qty *price as decimal(24,2))) total_with_cast_after_mul
  , sum(qty *price) / sum(qty) as avg_price_with_no_cast
  , cast(sum(qty *price) as decimal(18,2)) / sum(qty)  as avg_price_with_cast_before_div
  , sum(cast(qty *price as decimal(24,2))) / cast(sum(qty) as decimal(24,2)) as avg_price_with_cast_before_sum
  into dbo.res
  from dbo.test

select * from dbo.res
  
select name, precision, scale from sys.columns
where object_id = object_id('dbo.res', 'U')

Qty	Price	total	sum1	sum2	sum3	sum4
550.0000000000	239.4101000000	131675.555000000	273130.84	273130.84	237.50507826086956521739	237.50507826086956521739
100.0000000000	238.5528000000	23855.280000000	273130.84	273130.84	237.50507826086956521739	237.50507826086956521739
500.0000000000	235.2000000000	117600.000000000	273130.84	273130.84	237.50507826086956521739	237.50507826086956521739

system_type_name
decimal(38,9)

total	qty_all	total_with_cast_before_mul	total_with_cast_after_mul	avg_price_with_no_cast	avg_price_with_cast_before_div	avg_price_with_cast_before_sum
273130.835000000	1150.0000000000	273130.835000	273130.84	237.505073	237.505078260869	237.505078

name	precision	scale
total	38	9
qty_all	38	10
total_with_cast_before_mul	38	6
total_with_cast_after_mul	38	2
avg_price_with_no_cast	38	6
avg_price_with_cast_before_div	38	12
avg_price_with_cast_before_sum	38	6