SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tblOrderDetails
    (OrderDetailId int, Quantity int, UnitCost int, OrderId int)
;
    
INSERT INTO tblOrderDetails
    (OrderDetailId, Quantity, UnitCost, OrderId)
VALUES
    (1, 100, 30, 1),
    (2, 50, 40, 2),
    (2, 10, 15, 2),
    (2, 20, 30, 2),
    (2,0,0,2)
;


CREATE TABLE tblOrder
    (OrderId int, OrderNo varchar(5), OrderDate date)
;
    
INSERT INTO tblOrder
    (OrderId, OrderNo, OrderDate)
VALUES
    (1, 'ORD 1', '2021-01-01 00:00:00'),
    (2, 'ORD 2', '2021-01-03 00:00:00'),
    (2, 'ORD 2', '2021-01-03 00:00:00')
;
8 rows affected

select *
from   tblOrderDetails;
select *
from   tblOrder


OrderDetailId
Quantity
UnitCost
OrderId


1
100
30
1

2
50
40
2

2
10
15
2

2
20
30
2

2
0
0
2




OrderId
OrderNo
OrderDate


1
ORD 1
2021-01-01

2
ORD 2
2021-01-03

2
ORD 2
2021-01-03


select   o.OrderNo
        ,od.total
        ,o.OrderDate
from
(
select   OrderId
        ,sum(Quantity*UnitCost) as total
from     tblOrderDetails 
group by OrderId
) od join tblOrder o on o.OrderId = od.OrderId


OrderNo
total
OrderDate


ORD 1
3000
2021-01-01

ORD 2
2750
2021-01-03

ORD 2
2750
2021-01-03


SELECT tblOrder.OrderNo, 
SUM(tblOrderDetails.UnitCost*tblOrderDetails.Quantity) AS Total, 
OrderDate 
FROM tblOrderDetails 
INNER JOIN tblOrder ON tblOrderDetails.OrderId = tblOrder .OrderId 
GROUP BY tblOrder.OrderNo,OrderDate;


OrderNo
Total
OrderDate


ORD 1
3000
2021-01-01

ORD 2
5500
2021-01-03