November 25, 2006 at 1:08 pm
I’ve been having some difficulty trying to create a sproc to sum values in a data table based on the value in another field of the same table. My sproc also needs to be able to output the department name from a second table. Simplifying the data table’s layout (omitting the primary key, for instance), I basically have an Inventory table as below:
DeptID int
TypeCode nchar(2)
Sold int
Bought int
I also have a Departments table as follows:
DeptID int
DeptName nvarchar(20)
My attempts at writing the SQL for the sproc have basically been something similar to what I’ve pasted below:
SELECT Departments.DeptName, Inventory.TypeCode,
SUM(Sold) AS NumberOfItemsSold,
SUM(Bought) as NumberOfItemsBought,
(SELECT SUM(Sold) FROM Inventory WHERE TypeCode=’AB’) AS ABItemsSold
FROM Inventory INNER JOIN
Departments ON Inventory.DeptID = Departments.DeptID
GROUP BY Inventory.TypeCode, Departments.DeptName
The problem is the ABItemsSold gets repeated for each row in the department regardless of whether or not there’s been any sales of an item with a TypeCode equal to ‘AB’; in other words, no sales of an item should equal 0 for the ABItemsSold column in the resultset. If there are a total of 8 items with a TypeCode of ‘AB’ in the data table, every row that comes back in the resultset says there are 8 ABItemsSold. The only way I could think of grabbing the total of 'AB' items sold was to use the Select subquery inside the primary Select statement. That apparently doesn't work.
Can anyone help me with my SQL statement?
November 26, 2006 at 11:52 pm
The following will work for you:
SELECT Departments.DeptName, Inventory.TypeCode,
SUM(Sold) AS NumberOfItemsSold,
SUM(Bought) as NumberOfItemsBought,
Case Inventory.TypeCode
When 'AB' then SUM(Sold)
Else 0
End AS ABItemsSold
FROM Inventory INNER JOIN
Departments ON Inventory.DeptID = Departments.DeptID
GROUP BY Inventory.TypeCode, Departments.DeptName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply