1

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.

DontEatMyCookies
  • 188
  • 1
  • 12

2 Answers2

1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1

Don't take the customer id from the orders table. Take it from the customers table. If you do so, this might work in your database:

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

Note that the WHERE clause does not need to repeat the conditions in the ON clause.

Your version won't work in standard SQL because od.CustomerId is not unique in OrderDetails. Many databases don't support this, so in these you need the additional columns:

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

Even so, it is much, much better to take all columns from the same table. That would allow the SQL optimizer to use indexes on Customers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hmm, I wonder if by the laws of transitive closure (given the join predicate), the functional dependency of `c.*` columns on `od.CustomerID` should be usable... – Lukas Eder May 04 '20 at 12:38
  • @LukasEder . . . That would surprise me. It is possible, but it would surprise me in practice. In practice, attributes and statistics do not "transit" across `join` conditions. It would be complicated, because the `GROUP BY` could have columns from other tables -- even two id columns that are each unique in their own tables. – Gordon Linoff May 04 '20 at 13:15