Select Statement - 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 2005 Forums

Transact-SQL (2005)

Select Statement

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 20:05:17

The following SQL gives an error. How can the same result be achieved.

DECLARE @var_order_item_id BIGINT

SELECT DISTINCT
o.[order_id]
@var_order_item_id=oi.order_item_id
,(SELECT SUM(quantity) FROM Order_Items_Shipping_Comments AS oisc WHERE oisc.order_item_id=@var_order_item_id)
FROM Orders AS o

Thanks a lot.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-29 : 20:10:35

can you explain what are you trying to do here ?

KH
[spoiler]Time is always against us[/spoiler]

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 20:13:12

i am getting the SUM of quantities from the Order_Items_Shipping_Comments table for every row in the orders table which has the same order_item_id

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-29 : 20:34:12

I am getting this message

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Can we acheive this.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 00:59:30

SELECT
DISTINCT
o.[order_id],
order_item_id,
oisc.quantity
FROM Orders AS o INNER JOIN
(SELECT SUM(quantity) as quantity FROM Order_Items_Shipping_Comments
GROUP BY order_item_id
)AS oisc ON oisc.order_item_id=o.order_item_id

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-30 : 01:03:58

I'm not sure if madhivanan code will work since order_item_id is not listed in the select list of the sub query, but if it doesn't, just try this.


SELECT 
DISTINCT 
o.[order_id],
order_item_id,
oisc.quantity
FROM Orders AS o INNER JOIN 
(SELECT Order_Item_id, SUM(quantity) as quantity FROM Order_Items_Shipping_Comments 
GROUP BY order_item_id
)AS oisc ON oisc.order_item_id=o.order_item_id

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 01:43:37

quote:
Originally posted by Vinnie881

I'm not sure if madhivanan code will work since order_item_id is not listed in the select list of the sub query, but if it doesn't, just try this.

SELECT
DISTINCT
o.[order_id],
order_item_id,
oisc.quantity
FROM Orders AS o INNER JOIN
(SELECT Order_Item_id, SUM(quantity) as quantity FROM Order_Items_Shipping_Comments
GROUP BY order_item_id
)AS oisc ON oisc.order_item_id=o.order_item_id

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

You are correct. Order_Item_id should be used

Madhivanan

Failing to plan is Planning to fail

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-30 : 09:35:44

Thanks a lot Vinnie, Madhivanan. I am using Order_Item_id in the sub query and the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources