sql - Subquery Executing Problems -

the code below correct show me

select top 3 p.categoryid,sum(od.quantity) "order details" od             inner join             products p             on od.productid=p.productid             group p.categoryid             order sum(od.quantity) desc 

the result is

categoryid   sum 1            9532 4            9149 3            7906 

but encountered error when try subquery it.

the code below current problem cause error. error "only 1 expression can specified in select list when subquery not introduced exists."

select categoryname categories categoryid =     (     select top 3 p.categoryid,sum(od.quantity) "order details" od     inner join     products p     on od.productid=p.productid     group p.categoryid     order sum(od.quantity) desc     ) 

i not sure how solve or change code in way , did try not group categoryid failure did not show result.

the desired result should be

categoryname      beverages dairy products confections 

mysql uses limit, not top. issue tagged mysql, not sql server, i'm answering accordingly.

select   p.categoryid, sum( od.quantity )   orderdetails od   inner join products p     on od.productid=p.productid group   p.categoryid order   sum( od.quantity ) desc limit 3; 

in spirit of @kjell suggests, mysql doesn't allow in predicate way. use inner join instead:

select   categoryname   categories   inner join    (     select       p.categoryid, sum( od.quantity )           orderdetails od       inner join products p         on od.productid=p.productid     group       p.categoryid     order       sum( od.quantity ) desc     limit 3   ) quants   on quants.categoryid = categories.categoryid; 

if using sql server, please update question , either use in predicate or use inner join example top instead of limit:

select   categoryname   categories   categoryid in   (     select top 3       p.categoryid           orderdetails od       inner join products p         on od.productid=p.productid     group       p.categoryid     order       sum( od.quantity )   ) 

-- or

select   categoryname   categories   inner join    (     select top 3       p.categoryid, sum( od.quantity ) q           orderdetails od       inner join products p         on od.productid=p.productid     group       p.categoryid     order       sum( od.quantity )   ) quants   on quants.categoryid = categories.categoryid