How to use subquery when return a multiple values in main query?
Example :
select ProductID from purchaseorderitemdetail where productid in
(select top 5 ProductID,SUM(Amount) from PurchaseOrderItemDetail group by ProductID order by SUM(amount) desc)
Thanking You
Hardik Bhavsar
Jaganathan BantheswaranPosted Nov 25, 2013, 2:21 AM
Sub query should not return multiple values. So you should use join here.
select top 5 p.ProductID, SUM(pod.Amount) from purchaseorderitemdetail p
join PurchaseOrderItemDetail pod on pod.productid = p.ProductID
group by pod.ProductID order by SUM(pod.amount) desc
Ahmar HusainPosted Nov 25, 2013, 2:17 AM
After having a look on your query i would like to ask u a question you want to select records on the basis of product id (you are filtering the records on record id) , right ? then tell me why do you want SUM(Amount) in your subquery "select top 5 ProductID,SUM(Amount) from PurchaseOrderItemDetail....". why dont u sum Amount in the main query .