Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Question Unanswered: Help with Sum function and two tables. New user.

    I am working with the following tables:

    CREATE TABLE product (
    product_id number(11,0),
    product_name varchar2(50),
    product_price number(6,2),
    CONSTRAINT product_pk PRIMARY KEY (product_id) );

    CREATE TABLE orderline (
    order_id number(11,0),
    product_id number(11,0),
    quantity number(11,0),
    CONSTRAINT orderline_pk PRIMARY KEY (order_id, product_id),
    CONSTRAINT orderline_fk1 FOREIGN KEY (order_id)
    REFERENCES ordertable (order_id),
    CONSTRAINT orderline_fk2 FOREIGN KEY (product_id)
    REFERENCES product (product_id) );

    I am trying to list the Product_ID, Product_Name and total quanity per product. I am also trying to list these in ascending order of product_id

    I created the following but it is just not working:

    select product_id, product_name,
    sum (quantity) as Total_quantity
    from orderline, product
    group by product_id
    order by product_id;

    My problems arise when I start adding in another table. I was able to do everything in a single table without having the product_name, for example:

    select product_id,
    sum(quanitity) as Total_Quantity
    from orderline
    group by product_id
    order by Total_Quantity;

    But now I need the Product_name from the Product table in there.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    I would correct you: it is working as you coded it.

    It looks like you never met with a basic SQL join which does what you require. Here is what wikipedia says about it: https://en.wikipedia.org/wiki/Join_%28SQL%29 You may be interested in inner equi-join. Although that article is written for all RDBMSs, it is also applicable to Oracle.

    For specific Oracle join features, consult SQL Language Reference for your Oracle version. It is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The first query lacks in WHERE clause (so you are producing a cross join which results in Cartesian product); furthermore, its GROUP BY clause is wrong as it misses one of the columns (PRODUCT_NAME).

    So, if you apply the above to your problem, what do you get?

  4. #4
    Join Date
    Oct 2015
    Posts
    2
    I finally went with a basic join and came out with the table I was looking for:

    select product_id, product_name, sum(quantity) as Total_Quantity
    from orderline natural join product
    group by product_id, product_name
    order by product_id;

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK. Do you understand why it returned results you expected? In other words, do you know what NATURAL JOIN is and how it works? It would be a shame if you use the same principle next time and become surprised if it suddenly doesn't work (while THIS example above does).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •