Select within a select - SQL Server Forums

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

Transact-SQL (2000)

Select within a select

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-11-09 : 15:11:24

I am trying to calculate the amount of a total price a detail record represents. I have a header and a detail table. On the header is a total and on the detail is the cost for each item. I need to total the cost and calculate the percent a detail is of the total cost and then take that against the total selling price. What amount of the selling price each piece contributed.

I can get the individual selects to work but am not sure how to join them or if I need to use a cursor.

Select TransId, ItemId, ItemPrice, ComponentId, (ItemPrice * (Select ComponentCost/(Select TransId, ComponentId, Sum(ComponentCost) from Detail Group By TransId, ItemId)) AS ComponentCost From Header

vmon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 15:40:30

Since you don't bother to include DDL, here is what you get.

Select		h.TransId,
		h.ItemId,
		h.ItemPrice,
		h.ComponentId,
		h.ItemPrice * h.ComponentCost / q.x AS ComponentCost
From		Header h
inner join	(
			Select		TransId,
					Sum(ComponentCost) x
			from		Detail
			Group By	TransId,
					ItemId
		) q on q.transid = h.transid

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources