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
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”. 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,
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
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