QUESTION.. - 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)

QUESTION..

Author

Topic

praveen.bademi
Starting Member

3 Posts

Posted - 2008-05-16 : 04:09:36

i have created 2 tables orderdetails and catalog
table structures are as follows.
ORDERDETAILS TABLE
orderno bookid qty
1 51 20
2 51 25
3 53 15
4 52 40
5 54 10

CATALOG TABLE
BOOKID TITLE AUTHORID PUBID CATID
51 dbms 1 11 101
52 dbms 2 11 101
I need bookid having maximum quantity.
i used the query
select max(sum(qty)) as xyz,o.bookid FROM catalog c,Orderdetails o
WHERE o.bookid = c.bookid
group by o.bookid

but i got error.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I NEED TO GET ONLT MAXIMUM QUNATITY AFTER APPLYING GROUPBY CLAUSE IE 45 OF BOOKID 51.
KINDLY HELP ME

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-05-16 : 04:14:51

select top 1 bookid
,sum(qty) as total
from orderdetails
group by bookid
order by sum(qty) desc

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

praveen.bademi
Starting Member

3 Posts

Posted - 2008-05-16 : 04:33:46

Thank you very much
but I WANT ONLY MAXIMUM QUANTITY I DONT WANT BOOKID
BECAUSE I AM PASSING THAT QUANTITY IN A SUBQUERY

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-16 : 08:00:20

Did you consider removing the book_id from the select? ->

select top 1 sum(qty) as total
from orderdetails
group by bookid
order by sum(qty) desc

--
Lumbago

praveen.bademi
Starting Member

3 Posts

Posted - 2008-05-17 : 05:46:56

thank you for ur reply but

this query is giving an error because we are grouping it on bookid

and we are selecting only quantity.
so i need ur further help

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-05-17 : 07:30:02

declare @t1 table (orderno int, bookid int, qty int)
insert @t1
select 1, 51,20
union all select 2, 51,25
union all select 3, 53,15
union all select 4, 52,40
union all select 5, 54,10
SELECT * FROM @t1

declare @t2 table (BOOKID int, TITLE varchar(20), AUTHORID int,PUBID int,CATID int)
insert @t2
select 51,'dbms',1,11,101
union all select 52,'dbms',2,11,101

SELECT * FROM @t2

select max(xyz) from
(
select sum(qty) as xyz,o.bookid FROM @t2 c,@t1 o
WHERE o.bookid = c.bookid
group by o.bookid
) a

chandan Joshi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources