Internal communication with Sum Aggregate in SQL Server

Question

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

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

The desired result should contain ItemName, SpecificationName and SUM (Quantity). How to use the aggregation function in internal joins?

Answer 1

You summarize the desired column and group by the remainder, the fact that the columns are the result of the join does not matter 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