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
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
SQL SUM() and COUNT() using variable SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
Show activity on this post. This is assuming you don't have gaps between the first start date and last end date. If you do, you may need to edit the inner select. But the idea is to have the inner select determine your date ranges (and ignore the categories), and then have the outer one sum up your months.
To sum rows with same ID, use the GROUP BY HAVING clause.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With