Monday, December 21, 2009

SQL Aggregate Functions

Recently I came across a problem where I needed to get a sum of a quantity from one table, yet also retrieve all the rows from a different table. I spent a little time on this, and here's what I came up with after wrestling with it for awhile:

select i.*, isnull(d.SumQty, 0) as WhQty
FROM Item i
left outer join
(select det.ItemKey, SUM(det.WarehouseQty) as SumQty
From ItemWarehouseDetail det GROUP BY det.ItemKey) d
ON d.ItemKey = i.ItemKey

What I am doing is retrieving all the rows from the Item table. I have another table, ItemWarehouseDetail that has multiple records that contain different quantities for each item. I need to return the sum of these quantities based on the ItemKey in addition to all the Item fields. This will probably come in useful in the future, so I'm posting here so I will be able to refer back to it. I hope that this helps someone!