Skip to content
Advertisement

Select Subquery Group By

I am having an issue with this simple query. I get the error

“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.”

    SELECT TOP 100 PERCENT 
         dbo.Inventory.PARTNO
        ,( SELECT ISNULL(SUM(dbo.PurchaseOrderReceived.QtyReceived), 0)
             FROM dbo.PurchaseOrderReceived 
             JOIN dbo.PurchaseOrderlineItems 
               ON dbo.PurchaseOrderReceived.POLIID = dbo.PurchaseOrderlineItems.POLIID 
             JOIN dbo.Inventory 
               ON dbo.PurchaseOrderlineItems.InvMasID = dbo.Inventory.InvMasID 
             JOIN dbo.Duties Duties_1 ON dbo.Inventory.DutyClass = Duties_1.DutyID
            WHERE (Duties_1.DutyClass = 252) 
              AND (dbo.PurchaseOrderlineItems.Deleted = 0)
              AND (dbo.PurchaseOrderReceived.ReceivedDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102))
         GROUP BY dbo.Inventory.PARTNO
        ) AS Purchased
       ,ISNULL(SUM(dbo.OrderItems.QtyShipped), 0) AS Ordered
       ,ISNULL(SUM(DISTINCT dbo.MRP.QtyOnHand), 0) AS QOH
  FROM dbo.Orders 
  JOIN dbo.OrderItems
  JOIN dbo.Inventory 
    ON dbo.OrderItems.InvMasID = dbo.Inventory.InvMasID 
    ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
  JOIN dbo.MRP ON dbo.Inventory.InvMasID = dbo.MRP.InvMasID
  JOIN dbo.Duties ON dbo.Inventory.DutyClass = dbo.Duties.DutyID

 WHERE (dbo.Orders.ShipDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102)) 
   AND (dbo.Duties.DutyClass = 252)
 GROUP BY dbo.Inventory.PARTNO

I have tried working through this problem and know that there is a simple solution I am missing. The subquery, on its own, retrieves the information I am looking for, as does the main query, when separated. Thanks for any help!

Advertisement

Answer

put this condition AND dbo.Orders.PARTNO = dbo.Inventory.PARTNO and Top 1

SELECT TOP 100 PERCENT 
         dbo.Inventory.PARTNO
        ,( SELECT Top 1 ISNULL(SUM(dbo.PurchaseOrderReceived.QtyReceived), 0)
             FROM dbo.PurchaseOr,derReceived 
             JOIN dbo.PurchaseOrderlineItems 
               ON dbo.PurchaseOrderReceived.POLIID = dbo.PurchaseOrderlineItems.POLIID 
             JOIN dbo.Inventory 
               ON dbo.PurchaseOrderlineItems.InvMasID = dbo.Inventory.InvMasID 
             JOIN dbo.Duties Duties_1 ON dbo.Inventory.DutyClass = Duties_1.DutyID
            WHERE (Duties_1.DutyClass = 252) 
              AND (dbo.PurchaseOrderlineItems.Deleted = 0)
              AND (dbo.PurchaseOrderReceived.ReceivedDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102)) 
AND dbo.Orders.PARTNO = dbo.Inventory.PARTNO
         GROUP BY dbo.Inventory.PARTNO
        ) AS Purchased
       ,ISNULL(SUM(dbo.OrderItems.QtyShipped), 0) AS Ordered
       ,ISNULL(SUM(DISTINCT dbo.MRP.QtyOnHand), 0) AS QOH
  FROM dbo.Orders 
  JOIN dbo.OrderItems
  JOIN dbo.Inventory 
    ON dbo.OrderItems.InvMasID = dbo.Inventory.InvMasID 
    ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
  JOIN dbo.MRP ON dbo.Inventory.InvMasID = dbo.MRP.InvMasID
  JOIN dbo.Duties ON dbo.Inventory.DutyClass = dbo.Duties.DutyID

 WHERE (dbo.Orders.ShipDate > CONVERT(DATETIME, '2018-08-22 00:00:00', 102)) 
   AND (dbo.Duties.DutyClass = 252)
 GROUP BY dbo.Inventory.PARTNO
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement