Retrieve value from a table with the value from so

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

Retrieve value from a table with the value from so

Author

Topic

lselvaraj
Starting Member

2 Posts

Posted - 2008-04-11 : 03:03:51

I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.

select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)

In the above query am getting the productnum and quanity and it looks like this

productnum quantity
abc 6
ttt 3
sss 1

What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.

my query is
select * from product where productnum in (
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)

Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-11 : 03:45:35

ok, let's sart simple...

your first query. why not just...


select e.productnum, SUM(e.quantity) as quantity
from orderlineitem e 
where e.parentOrderlineitemid is null 
group by e.productnum
order by sum(e.Quantity) desc

so your second would be like...


select p.somecol,e.productnum, SUM(e.quantity) as quantity
from orderlineitem e 
	join product p on p.productnum = e.productnum
where e.parentOrderlineitemid is null 
group by e.productnum, p.SomeCol
order by sum(e.Quantity) desc

lselvaraj
Starting Member

2 Posts

Posted - 2008-04-15 : 05:59:41

Hey thanks for the reply. Yes your method worked.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources