I am trying to create a table that includes the customer ID, last name, first name, and the customer?s total balance (the sum of price for all orders).
CUSTOMER
CUSTOMER_ID LAST FIRST STREET CITY STATE ZIP
———– ——- ——- ——— ——- —– ——
124 Martin Dan 418 Pine Grant MI 49219
1552 Adams Joan 3435 Elm Harper TN 39805
… … … … … … …
ORDER
ORDER_NUM CUSTOMER_ID ORDER_DATE PRICE
——— ———– ———- ——-
10024 5642 12/8/1999 501.89
10025 124 30/9/1999 215.95
10026 4542 11/1/2000 570.25
10027 124 12/2/2000 420.60
… … … …
The result should be a table that looks like this:
BALANCE
CUSTOMER_ID LAST FIRST TOTAL_BALANCE
———– ——- ——- ————-
1552 Adams Joan 845.56
5642 Collins Chip 760.01
4542 Ira Sally 570.25
… … … …
Unfortunately, there are several mistakes.
The following SQL commands have errors and are not yielding the correct result. I need to modify the following code to get the desired table above.
SQL>CREATE TABLE balance
2(customer_id NUMBER(7), last CHAR(10), first CHAR(10), total_balance CHAR(7));
SQL>INSERT INTO balance
2 SELECT customer.customer_id, customer.last, customer.first, sum(customer.price)
3 FROM customer, orders
4 WHERE customer.customer_id = order.customer_id
Can anyonen help me? Thanks