Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query with SUM on column in JOINed table

Tags:

sql

join

sum

derby

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 
like image 226
Henry Ing-Simmons Avatar asked May 03 '12 10:05

Henry Ing-Simmons


People also ask

How do I sum a column in a table in SQL?

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.

Can we use sum and count together in SQL?

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.

How do I sum after GROUP BY in SQL?

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.

How do I sum a column with the same ID?

To sum rows with same ID, use the GROUP BY HAVING clause.


1 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
like image 64
Romil Kumar Jain Avatar answered Sep 20 '22 12:09

Romil Kumar Jain