By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dbo.test
(
Qty DECIMAL(24,10),
Price DECIMAL(24,10)
)
insert dbo.test (qty, price)
values
(550 , 239.4101)
,(100 , 238.5528)
,(500 , 235.2)
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 |