Geek asks and answers

SQL query with SUM by column in JOINed table

I have two tables in the 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 the query to return all the order information from the Orders table, as well as the total number of products associated with this order.

I tried to think that this would work, but I get an error - "Column link identifier" is invalid. When the SELECT list contains at least one aggregate, then all records 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
+5
source share
2 answers
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
+9
source

I think you need the group by command:

GROUP BY orders.ID,orders.UserID,orders.PurchaseDate,orders.TotalPrice
0
source

More articles:


All Articles