New to SQL Need Help

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? :confused:

Hi Cj2001

You have a aggregation function
"SUM(order.price)" in your query , and GROUP BY works on groups of rows.

So your GROUP BY Clause should HAVE all those columns (that are in the SELECT clause) and those that are NOT used with aggregation functions.

So the query should be:
SELECT Cust.customer_id,cust.last,cust.first, sum (ord.price)
From customer cust,order ord
WHERE cust.customer_id = ord.customer_id
GROUP BY cust.customer_id,
cust.last,cust.first;


Note the GROUP BY CLAUSE

RULE OF THUMB:

Either have aggregation functions in SELECT clause or specify the columns in the GROUP BY clause.

HTH

I will try this. One other small thing I noticed in your FROM clause is worded differently then mine


Yours: From customer cust,order ord
Mine: From customer,order

Why the abrevations following the table name?

Just new at this and need to understand for future reference.

Tanks again for your help.:)

What you see in my FROM clause is the table name and its alias

as
SELECT....
FROM customer cust

The alias helps you abbreviate length table names and makes your queries easy to read also.

For SQL basics, you can also read the Oracle SQL Reference

http://download-west.oracle.com/docs...a96540/toc.htm

HTH

That clear some of the cob webs and thanks for everything and the link. :)

Hello,
I was finally able to try the SQL code you provided to me yesterday and it came with a "no rows selected" message.

This is what I entered:
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,customer.last,customer.first;

Any suggestions or did I do something wrong

When I removed the sum(order.price) then I got rows :confused: