Need help with a Group By query

Hi. I am trying to write a query that counts up the total quantity ordered of each ProductCode for all open orders. The idea is that the person pulling the orders can pull the total quantities for all the products ordered at the beginning of the shift and then pack the orders from those already pulled products. There can be multiple of each product code per order. We just want one line for each unique ProductCode with the total quantity ordered for all orders that are “processing”. I tried to do it below, but am getting an error that says:

Your SQL is invalid: Column ‘OrderDetails.ProductName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas what I’m doing wrong?

SELECT 
SUM(od.Quantity)
,od.ProductCode
,od.ProductName 
FROM 
OrderDetails as od 
JOIN 
Orders as o 
ON o.OrderID=od.OrderID 
WHERE o.OrderStatus='Processing' 
GROUP BY od.ProductCode

With a GROUP BY clause, everything in the SELECT has to be in the GROUP BY.

Also, if your first item isn’t being labeled as “Quantity”, you’ll need to give it an alias, I think.

HTH,

:slight_smile:

I’m not sure I follow your answer. This is what I tried based on how I interpreted your answer. It is giving me an error saying that “TotalQuantity” is an invalid column name.

SELECT SUM(od.Quantity) AS TotalQuantity
,od.ProductCode
,od.ProductName 
FROM 
OrderDetails as od 
JOIN Orders as o 
ON o.OrderID=od.OrderID 
WHERE o.OrderStatus<>'Processing' 
GROUP BY 
od.ProductCode
,TotalQuantity
,od.ProductName

Well, I did say “IF your first item isn’t being labeled”. :smile: I guess it might be getting “Quantity” as the label. Try removing the alias, and swapping “TotalQuantity” with “od.Quantity” in the GROUP BY.

HTH,

:slight_smile:

PS. What flavour of SQL are you using? Oracle? MS-SQL? (I’m guessing it isn’t MySQL).

The od.Quantity should not be required to be in the GROUP BY since it is used in an Aggregate function.

Try removing it, and having both od.ProductCode and od.ProductName in the query.

Though, assuming you are using a different database than MySQL (where this would not be an issue). It might be more effective to a sub query if the database supports it: (Though, as always benchmark it to verify if it is more effective)

SELECT temp.QuantityTotal , temp.ProductCode , detail.ProductName FROM (SELECT SUM(od.Quantity) as QuantityTotal ,od.ProductCode FROM OrderDetails as od INNER JOIN Orders as o ON (o.OrderID=od.OrderID) WHERE o.OrderStatus<>'Processing' GROUP BY od.ProductCode) as temp INNER JOIN OrderDetails as detail USING (ProductCode);

[quote=“whitemank, post:3, topic:118020, full:true”]
I’m not sure I follow your answer. [/quote]
problem is, you followed it too closely :wink:

instead of “everything in the SELECT has to be in the GROUP BY”, it should be “every non-aggregate in the SELECT has to be in the GROUP BY”

so, you had the aggregate in the GROUP BY, and it needs to come out –

SELECT od.ProductCode , od.ProductName , SUM(od.Quantity) AS TotalQuantity FROM Orders as o INNER JOIN OrderDetails as od ON od.OrderID = o.OrderID WHERE o.OrderStatus <> 'Processing' GROUP BY od.ProductCode , od.ProductName
notice one other subtle change i made – the first table in the FROM clause is the orders table, because you have a restrictive WHERE condition on it

it’s the difference between “get all orders that aren’t processing, and then get all order details for only those orders” and “get all orderdetails for all orders and throw away the ones that don’t belong to orders that aren’t processing”

fortunately the optimizer figures out that the first approach is better, and executes it that way even if you write it the other way, which is why i prefer to write it my way

if the above didn’t make sense, please just ask

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.