Error Subquery uses ungrouped column "i.colorid" from outer query

Question

Following is the query

select (select description from Colors where ID = I.ColorID) AS Color,
O.Sono,
O.Customer,
SUM(L.Quantity) AS Quantity, 
O.shipdate,I.VapID,L.OrderID 
from Orderlines  L, Items I, Orders O 
where L.Item = I.SKU AND I.PanelID=3 
AND O.ID=L.OrderID 
AND O.OrderType = 'Sales Order' 
 AND (L.Status < 1 OR L.Status is Null) 
Order by Color, shipdate

This query is giving error "Subquery uses ungrouped column "i.colorid" from outer query"

Could you provide some sample data and expect result? What's DBMS did you use? — D-Shih
there is no need for this subquery: (select description from Colors where ID = I.ColorID) - join to colors table and select directly from there — Daniel Marcus
@DanielMarcus Yes i did that than it started giving following errors "Kernel error: ERROR: column "c.description" must appear in the GROUP BY clause or be used in an aggregate function" — RackM
This worked "select C.description,O.Sono,O.Customer,SUM(L.Quantity) AS Quantity, O.shipdate,I.VapID,L.OrderID from Orderlines L, Items I, Orders O, Colors C where L.Item = I.SKU AND I.PanelID=1 AND O.ID=L.OrderID AND O.OrderType = 'Sales Order' AND (L.Status < 1 OR L.Status is Null) group by C.description, shipdate,o.sono,O.Customer, i.vapid, l.orderid order by C.description, shipdate" — RackM

Daniel Marcus Daniel Marcus · Accepted Answer · 2018-04-17T15:24:46

select  description  AS Color,
O.Sono,O.Customer,SUM(L.Quantity) AS Quantity, O.shipdate,I.VapID,L.OrderID 
from Orderlines  L, Items I, Orders O, colors c  
where L.Item = I.SKU AND I.PanelID=3 AND O.ID=L.OrderID AND O.OrderType = 'Sales Order' 
and  c.ID = I.ColorID
AND (L.Status < 1 OR L.Status is Null) 
group by description,
O.Sono,O.Customer, O.shipdate,I.VapID,L.OrderID
Order by Color, shipdate

Error Subquery uses ungrouped column "i.colorid" from outer query

1 Answers