how to multiply data in different columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to multiply data in different columns?

Author

Topic

suizhun
Starting Member

2 Posts

Posted - 2010-01-20 : 09:23:06

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 09:30:28

Because I see IFNULL():
Is that MYSQL?

No, you're never too old to Yak'n'Roll if you're too young to die.

suizhun
Starting Member

2 Posts

Posted - 2010-01-20 : 09:44:31

Yes, I think that is MYSQL? is it not? Can it be solved nonetheless?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 10:17:35

So you don't know if this is MYSQL then how can we know?
This is a forum for MS SQL Server and so it is possible that a given solution to your problem will not work in MySQL.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources