Results 1 to 7 of 7

Thread: New to SQL Need Help

  1. #1
    Join Date
    Mar 2003
    Location
    CA
    Posts
    10

    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?

  2. #2
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18
    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

  3. #3
    Join Date
    Mar 2003
    Location
    CA
    Posts
    10

    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.

  4. #4
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18

    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

  5. #5
    Join Date
    Mar 2003
    Location
    CA
    Posts
    10

    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.

  6. #6
    Join Date
    Mar 2003
    Location
    CA
    Posts
    10

    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

  7. #7
    Join Date
    Mar 2003
    Location
    CA
    Posts
    10

    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
  •