SQL Server 2017

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

create table orderitems 
(
orderId int,
itemid int,
productId int,
itemprice decimal(18,2),
quantity int
)
create table orders
(
orderId int,
shipAmount decimal(18,2),
TaxAmount decimal(18,2)
)
insert into orderitems (orderId,itemid,productId,itemprice,quantity) values
(1,1,2,1199,1),
(2,2,8,489.99,1),
(3,3,1,2517,1),
(3,4,9,415,1),
(4,5,2,1199,1),
(5,6,10,299,1),
(6,7,10,299,1)
7 rows affected

insert into orders (orderId,shipAmount,TaxAmount) values
(1,5,58.71),
(2,5,58.72),
(3,10,58.73),
(4,10,58.74),
(5,5,58.75),
(6,5,58.76);
6 rows affected

select * from orders


orderId
shipAmount
TaxAmount


1
5.00
58.71

2
5.00
58.72

3
10.00
58.73

4
10.00
58.74

5
5.00
58.75

6
5.00
58.76


create function udfMyTable (@orderID  as int)
returns decimal(18, 2)
as
begin
  declare @res as decimal(19, 2);
  
  Select
    @res = SUM(itemprice*Quantity)
  from orderitems
  where
    orderID = @orderID    

return @res;
end
alter table orderitems add SubTotal as dbo.udfMyTable(orderID)
alter table orders add SubTotal as dbo.udfMyTable(orderID)
Select * from orderitems


orderId
itemid
productId
itemprice
quantity
SubTotal


1
1
2
1199.00
1
1199.00

2
2
8
489.99
1
489.99

3
3
1
2517.00
1
2932.00

3
4
9
415.00
1
2932.00

4
5
2
1199.00
1
1199.00

5
6
10
299.00
1
299.00

6
7
10
299.00
1
299.00


select * from orders


orderId
shipAmount
TaxAmount
SubTotal


1
5.00
58.71
1199.00

2
5.00
58.72
489.99

3
10.00
58.73
2932.00

4
10.00
58.74
1199.00

5
5.00
58.75
299.00

6
5.00
58.76
299.00

orderId	shipAmount	TaxAmount
1	5.00	58.71
2	5.00	58.72
3	10.00	58.73
4	10.00	58.74
5	5.00	58.75
6	5.00	58.76

orderId	itemid	productId	itemprice	quantity	SubTotal
1	1	2	1199.00	1	1199.00
2	2	8	489.99	1	489.99
3	3	1	2517.00	1	2932.00
3	4	9	415.00	1	2932.00
4	5	2	1199.00	1	1199.00
5	6	10	299.00	1	299.00
6	7	10	299.00	1	299.00