join a coloumn showing sum of 2 data together

Question

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

Puneet Kankar · Answer

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 Vaghasiya · Answer

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