sqljoinsumderby

SQL query with SUM on column in JOINed table

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
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