Doing math with recordsets in a view/function

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

SQL Server 2000 Forums

SQL Server Development (2000)

Doing math with recordsets in a view/function

Author

Topic

bdelaney
Starting Member

7 Posts

Posted - 2005-02-14 : 10:10:14

I have an inventory database, and I need to tally up all the totals for each purchase line that relates to a certain order number. I am trying to use a view for this. I get the order number in the view and call a function(UDF) to get the recordset, but im having trouble coming up with a way to loop through this recordset and do the math that i need to do to tally up totals. any suggestions?

cas_o
Posting Yak Master

154 Posts

Posted - 2005-02-14 : 10:27:29

Loop ? did you say Loop? Arrgghhhh runs away screaming...

not so many details in your post but, how about groupby ?

select
orders.ordernumber, sum(PurchaseLines.PurchasePrice) as total
from
orders join purchaselines on
purchaselines.ordernumber=orders.ordernumber
group by
orders.ordernumber

bdelaney
Starting Member

7 Posts

Posted - 2005-02-14 : 11:14:30

well im not so sure grouping will work.

Now all of this information is kept within the same table so there is no need for inner joins.

There is a unit price, quantity, and shipping for each purchase line, and i need to get the total of each line. Then get the total for every line that has the same order number. using your suggestion the math doesnt equal out the way it is supposed to.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-14 : 11:28:40

Something like this????


USE Northwind
SELECT OD1.OrderID, ProductID, UnitPrice , Quantity, UnitPrice * Quantity AS LineValue, OD2.TotalValue
FROM [Order Details] OD1 INNER JOIN (SELECT OrderID, SUM(UnitPrice * Quantity) AS TotalValue 
					FROM [Order Details] 
					GROUP BY OrderID) OD2
			 ON OD1.OrderID = OD2.OrderID

Andy

Beauty is in the eyes of the beerholder

bdelaney
Starting Member

7 Posts

Posted - 2005-02-14 : 12:04:13

that worked great. can you explain to me what exactly you are doing with the OD1 prefix?

cas_o
Posting Yak Master

154 Posts

Posted - 2005-02-14 : 12:42:14

Hes just using it to alias the [Order Details] table which is long to type and has a space meaning you need to type square brackets as well, so alias it as OD1 in the From clause and you can refer to it from there on as OD1 instead of [Order Details] every time.

;-]... Quack Waddle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources