Loading

join a coloumn showing sum of 2 data together

join a coloumn showing sum of 2 data together

Rehaan Ahmed

7y894

SELECT o.ProductID ,

p.ProductName ,

o.unitprice AS UnitCost ,

sum(o.Quantity) AS TotalUnitsSold ,

(sum(o.Quantity)*o.unitprice) AS FinalCost

FROM OrderDetails o

JOIN Products p ON o.ProductID = p.ProductID

GROUP BY o.ProductID ,

p.ProductName ,

o.unitprice

ORDER BY 1

I want one more column aside of Final Cost which gives total of Products, in short for chai it should be 2505.60+11772.00

EXPECTED o/p

ProductID ProductName UnitCost TotalUnitsSold FinalCost Total

1 Chai 14.40 174 2505.60 14277.60

1 Chai 18.00 654 11772.00 Null

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Puneet Kankar7y ago
Please check given below links.

You can simply achive by Calculating Running Total.

https://www.youtube.com/watch?v=BMzZmq12YJI

https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/

https://www.essentialsql.com/calculate-running-total-sql/

SELECT Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY Id) AS RunningAgeTotal,
AVG (StudentAge) OVER (ORDER BY Id) AS RunningAgeAverage
FROM Students
Jayesh Vaghasiya7y ago
Try This ....

declare @table table(productID int,ProductName varchar(16),
UnitCost numeric(18,2),SoldUnit int,FinalCost numeric(18,2))
insert into @table select 1,'Chai',14.40,174,2505.60
insert into @table select 1,'Chai',18,654,11772
insert into @table select 2,'chang',15.20,401,6095.2
insert into @table select 2,'chang',19,656,12464
select tb.productID,ProductName,UnitCost,SoldUnit,
case when ROW_NUMBER ( ) OVER ( PARTITION BY tb.productID ORDER BY tb.productID ) = 1 then final.FinalCost else null end FinalCost
from @table tb
left join
(
select productID,sum(FinalCost)FinalCost from @table
group by productID
)final on final.productID = tb.productID