-
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?
-
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
-
Thank You
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.
-
SQL Reference
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
-
Thanks
That clear some of the cob webs and thanks for everything and the link.
Last edited by cj2001; 03-13-2003 at 01:20 PM.
-
SQL didn't work
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
-
More
When I removed the sum(order.price) then I got rows
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|