Please help me with a subquery

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)

Please help me with a subquery

Author

Topic

bbeckstrom
Starting Member

1 Post

Posted - 2006-07-17 : 13:29:32

Okay, so here's my situation: I need the sum i do in the subquery to be grouped in the larger queries group by (i.e. I need to see the units sold to client # 123, and in the subquery I need to get the units sold and delivered to client #123)

Here is my query:

select sum(Orderline.Units) as Units,
count (OrderLine.OrderLine#) as lineitems, Client.Client#,
Client.Name, Client.City, Client.State,(select sum(OrderLine.Units)
from
OrderLine
where exists
(select OrderLine.OrderLine#,Client.Client#, Client.Name, Client.City, Client.State
from OrderLine
join Order on Order.Order#=OrderLine.Order#
join Client on Client.Client#=Order.ClienCode
join State on State.State=Client.State
where
OrderLine.Date>='7/1/2006' and OrderLine.Date<='9/30/2006'
and OrderLine.CancelDate is NULL and OrderLine.DeliveredDate is not NULL
and State.SalesPerson=236
group by OrderLine.OrderLine#,Client.Client#, Client.Name, Client.City, Client.State)
)as bookedUnits

from
OrderLine
join Order on Order.Order#=OrderLine.Order#
join Client on Client.Client#=Order.ClienCode
join State on State.State=Client.State
where
OrderLine.Date>='7/1/2006' and OrderLine.Date<='9/30/2006'
and OrderLine.CancelDate is NULL
and State.SalesPerson=236

group by Client.Client#, Client.Name, Client.City, Client.State

having count(OrderLine.OrderLine#) > 50

order by count (OrderLine.OrderLine#)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-18 : 03:05:55

i'd like to help but it's kinda confusing for me (not a 'textual' person)...

can you post your ddl for all tables involved and what variables are you using for criteria?

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 03:07:44

Post your table structure (DDL), some sample data and the result that you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources