multiple two column values with grouping by a column-CodePudding

I've two tables tblOrder and tblOrderDetails. I want to get order no, total price per order (Quantity*UnitCost) and OrderDate as given below.

Order No	Total	OrderDate
ORD 1	3000	01/01/2021
ORD 2	2750	01/03/2021

What I've tried is giving me quantity is not a part of aggregate function.

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

Table structures and data

Table tblOrder:

OrderId	OrderNo	OrderDate
1	ORD 1	01/01/2021
2	ORD 2	01/03/2021

Table tblOrderDetails:

OrderDetailId	Quantity	UnitCost	OrderId
1	100	30	1
2	50	40	2
2	10	15	2
2	20	30	2

CodePudding user response：

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

Fiddle

CodePudding user response：

Your requirements are not 100% clear, but maybe, you can just do this, without any subquery:

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;

To see the difference to Danny's answer - which might also be fine - have a look here: db<>fiddle