Software

Ask something
Discussion Locked

SQL*Plus Query

0 helpful
5 replies

by rawle

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

5 replies

by d-ced

Firstly, the total balance column shouldn't be a CHAR(7), it should be a NUMBER(7,2). This will avoid the data type conflicts you are probably getting.

Secondly, you are missing the GROUP BY clause that will allow you to do the SUM. And the pricecolumn is on the order table, not the Customer table. Also, try and avoid naming columns with just "first" or "last". Add the word name so that you know what it is. It also avoids conflicts if the word is actually a RESERVED word.

So the Table create should be
CREATE TABLE balance
( customer_id NUMBER(7),
last_name CHAR(10),
first_name CHAR(10),
total_balance NUMBER(7,2) );

Then the query should be:

INSERT INTO balance
SELECT customer.customer_id,customer.last_name,customer.first_name,sum(order.price)
FROM customer, order
WHERE customer.customer_id = order.customer_id;

Hopefully that helps you.

by pokvalitov

LAST and FIRST are reserved words. They used in sorts, if you want to place NULL values before or after non-empty values:

SELECT ...
ORDER BY sorting_field NULLS FIRST

This discussion is closed to new replies.