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

user_14995902 0
Viewed 28

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
subquery group-by sql
2 Answers

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
user_64290720

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
user_56959530
Related