Inner Join with Sum Aggregate function in SQL Server

Question

I have three tables StockSummary, Item, ItemSpecification. Here I want to join these three tables and to get Sum(StockSummary.Quantity). The main Columns are as follows:

TableA: StockSummary(ItemID, Quantity)
TableB: Item(ItemID, ItemName, SpecificationID)
TableC: ItemSpecification(SpecificationName, SpecificationID)

The desired result should give ItemName, SpecificationName and SUM(Quantity). How to use Aggregate function in Inner Joins?

Solution

You aggregate the desired column & group by the remainder, the fact that the columns are from the result of a join is not relevant in your case;

select
   b.ItemName,
   c.SpecificationName,
   sum(a.Quantity)
from
   tablea a
   inner join tableb b on b.ItemID = a.ItemID
   inner join tablec c on c.SpecificationID = b.SpecificationID
group by
   b.ItemName,
   c.SpecificationName