Trouble with SQL Statement

[cj2001]

I have 2 tables one called customer and one called order. I am attempting to create a 3rd table then insert data into the 3rd table using a select command. Here is what I have so far can you help me it won't work?

Create Table Balance
(customer_id number(7), Last char(10),first char(10),total_balance number (2,d);

Insert into Balance
select customer.customer_id,customer.last,customer.first, sum (order.price)
From customer,order
where customer.customer_id = order.customer_id
group by customer.customer_id;


What am I doing wrong here?

[xdjboyx]

Try the following code:

Insert into Balance (customer_id, Last, first, total_balance)
select customer.customer_id,customer.last,customer.first, sum (order.price)
From customer,order
where customer.customer_id = order.customer_id
group by customer.customer_id

If that doesn't work then you might need to join the tables. I think that it should work so give it a shot.

[cj2001]

Would you mind answering another quick question I have never worked with a decminal number in a table before so in my create table definition Total_Balance number(2,d) correct? If not how do you denote currency?

[xdjboyx]

Since you are using Total_Balance I would use "money" as the type.
This is how I would write the create table statement:

Create Table Balance
(customer_id int, [Last Name] char(10),[First Name] char(10),total_balance money)

Remember, if you have spaces in your column names, use []. I would avoid using Last and First since those are keywords in SQL (you'll notice that because the color of Last and First is blue).
Let me know if this helps. If this doesn't help then leave another message and I will respond ASAP.
Also, there is no such thing as number so use integers (int).

[cj2001]

Let me give this a try and thanks for your help. I will give you a holler if I need to.