I am trying to calculate the ending inventory cost using weighted average. I have figured out a column for the cost per item (CostperItem), and a column for item on hand(QuantityAvailable). Now I want to multiple those two columns together and get a new column for the result. how can i do that?

This is my code:

``````SELECT Inventory_Item.ItemName, (

SELECT SUM( TotalAmount / Quantity )
FROM Finance_Expenses, Inventory_ExternalOrder
WHERE OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01'
AND Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND Inventory_ExternalOrder.ExpID = Finance_Expenses.ExpID
) AS CostperItem, IFNULL( (

SELECT SUM( Quantity )
FROM Inventory_ExternalOrder
WHERE Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) - IFNULL( (

SELECT SUM( Quantity )
FROM Inventory_InternalOrder
WHERE Inventory_Item.ItemID = Inventory_InternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) AS QuantityAvailable
FROM Inventory_Item``````
3
Contributors
4
Replies
6
Views
8 Years
Discussion Span
Last Post by apegram

What is `IFNULL` ? You have to wrap your query in order to reference calculated columns. I can't verify the syntax, but this should work:

``````Select *, Sum(CostperItem) * Sum(QuantityAvailable)
From
(
SELECT Inventory_Item.ItemName, (

SELECT SUM( TotalAmount / Quantity )
FROM Finance_Expenses, Inventory_ExternalOrder
WHERE OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01'
AND Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND Inventory_ExternalOrder.ExpID = Finance_Expenses.ExpID
) AS CostperItem, IFNULL( (

SELECT SUM( Quantity )
FROM Inventory_ExternalOrder
WHERE Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) - IFNULL( (

SELECT SUM( Quantity )
FROM Inventory_InternalOrder
WHERE Inventory_Item.ItemID = Inventory_InternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) AS QuantityAvailable
FROM Inventory_Item
) as tbl
--Group by... ?
Order By Inventory_Item.ItemName``````

Edited by sknake: fixing tags

IFNULL(x, y) is a MySQL function that will return x unless it is null, in which case it returns y. I'm not sure of a SQL Server equivalent.

>>IFNULL(x, y) is a MySQL function that will return x unless it is null, in which case it returns y. I'm not sure of a SQL Server equivalent.

Ah, ok. Thanks for clarification. SQL Server would use `IsNull()` or `COALESCE()` .

Ah, ok. Thanks for clarification. SQL Server would use `IsNull()` or `COALESCE()` .

That's good to know. My POE has a major client that runs on MySQL and has database code with IFNULL all over the place. It's good to know if we ever have to migrate them over to SQL Server (where everybody else resides), it will be fairly simple to find/replace those functions.

Edited by apegram: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.