Problem with SQL group by and summing totals with a calculated column and sub query-CodePudding

I have this query that is working but I'm having trouble with the next step. It calls three different tables and returns a column of account numbers and a cost. I would like to sum the cost but grouped by account numbers. If I were starting out with just a two column table this is trivial. I'm running into issues doing it with a sub query for various reasons. Any input is apprecieated.

select top 100 tblPart.Account,(tblPartLocation.OnHand * tblPart.CostAverage)
as TotalCost 
from tblPartLocation
join tblPart on tblPart.Part = tblPartLocation.Part

Account  Cost
accnt1   3.56  
accnt1   4.78 
annct2   5.00
accnt1   1.23
accnt4   0.01
accnt5   1.01
accnt3   7.45

The expected output is

Account  Cost
accnt1   9.57  
accnt2   5.00 
annct3   7.45
accnt4   0.01
accnt5   1.01

CodePudding user response：

you can use sql sum() function then aggregate by Account.

select tblPart.Account, sum(tblPartLocation.OnHand * tblPart.CostAverage) as TotalCost 
from tblPartLocation
join tblPart on tblPart.Part = tblPartLocation.Part
group by tblPart.Account

CodePudding user response：

Another way to do this using SUM

select Account, sum(TotalCost) as TotalCost from (
    select tblPart.Account, (tblPartLocation.OnHand * tblPart.CostAverage) as TotalCost 
    from tblPartLocation
    join tblPart on tblPart.Part = tblPartLocation.Part
    ) as T1
group by Account