SQL query with SUM on column in JOINed table
derby, join, sql, sum
Solution
SELECT
app.orders.ID, app.orders.UserID, app.orders.PurchaseDate, app.orders.TotalPrice, SUM(app.orderItems.Quantity)
AS productCount
FROM app.orders JOIN app.orderItems ON app.orders.ID=app.orderItems.OrderID
group by app.orders.ID, app.orders.UserID, app.orders.PurchaseDate, app.orders.TotalPrice
Problem
I have two tables in a derby database that I want to query together. ``` Orders +----+--------+--------------+------------+ | ID | UserID | PurchaseDate | TotalPrice | +----+--------+--------------+------------+ | 1 | 1 | TIMESTAMP | 7.00 | OrderItems +---------+-----------+----------+ | OrderID | ProductID | Quantity | +---------+-----------+----------+ | 1 | 1 | 2 | ``` I want a query to return all the info on the order from the Orders table as well as the total number of product associated with that order. I tried this thinking it would work but get the error - "Column reference 'ID' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions." ``` SELECT orders.ID, orders.UserID, orders.PurchaseDate, orders.TotalPrice, SUM(Quantity) AS productCount FROM app.orders JOIN app.orderItems ON orders.ID=orderItems.OrderID ```