how to multiply data in different columns?

[suizhun]

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

[sknake]

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

[apegram]

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.

[sknake]

>>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() .

[apegram]

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.