Not in aggregate function or group by clause: org.hsqldb.Expression@59bcb2b6 in statement

I’m trying to group SUM(OrderDetails.Quantity) but keep getting the error Not in aggregate function or group by clause: org.hsqldb.Expression@59bcb2b6 in statement but since I already have an GROUP BY part I don’t know what I’m missing

SQL Statement:

SELECT OrderDetails.CustomerID, Customers.CompanyName, Customers.ContactName, SUM(OrderDetails.Quantity) 
FROM OrderDetails INNER JOIN Customers ON OrderDetails.CustomerID = Customers.CustomerID 
WHERE OrderDetails.CustomerID = Customers.CustomerID 
GROUP BY OrderDetails.CustomerID
ORDER BY OrderDetails.CustomerID ASC

I’m trying to create a table that shows customers and the amount of products they ordered, while also showing their CompanyName and ContactName.

Answer

Write this:

GROUP BY OrderDetails.CustomerID, Customers.CompanyName, Customers.ContactName

Unlike in MySQL, PostgreSQL, and standard SQL, in most other SQL dialects, it is not sufficient to group only by the primary key if you also want to project functionally dependent columns in the SELECT clause, or elsewhere. You have to explicitly GROUP BY all of the columns that you want to project.