I have two tables in a derby database that I want to query together.
我在derby数据库中有两个表，我想一起查询。
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.
我想要一个查询从Orders表返回订单上的所有信息以及与该订单关联的产品总数。
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."
我试过这个认为它会工作，但得到错误 - “列引用'ID'无效。当SELECT列表包含至少一个聚合时，所有条目必须是有效的聚合表达式。”
SELECT
orders.ID, orders.UserID, orders.PurchaseDate, orders.TotalPrice, SUM(Quantity)
AS productCount
FROM app.orders JOIN app.orderItems ON orders.ID=orderItems.OrderID
2 个解决方案
#1
9
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
#2
0
I think you need a group by clause like :
我认为你需要一个group by子句，如：
GROUP BY orders.ID,orders.UserID,orders.PurchaseDate,orders.TotalPrice
#1
9
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
#2
0
I think you need a group by clause like :
我认为你需要一个group by子句，如：
GROUP BY orders.ID,orders.UserID,orders.PurchaseDate,orders.TotalPrice