Results 1 to 3 of 3

Thread: Getting row information in Oracle

  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Getting row information in Oracle

    I am working on a query:

    Get the item_id, order_id, price from the order_line table where the price for an individual item's order is greater than the average of all orders for the item.

    Here is what I have so far to get the average for all of the items:

    SELECT order_id, AVG(price) FROM order_line
    GROUP BY order_id

    What can I do from here to get all of the prices (and item ids) where the price is greater than the average for each value?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    How about this.

    select a.item_id, a.rder_id, a.price
    from order_line a,
    (SELECT item_id, AVG(price) price FROM order_line
    GROUP BY item_id) b
    where a.item_id = b.item_id
    and a.price > b.price

  3. #3
    Join Date
    Aug 2003
    Posts
    15
    Thank you! It worked.

Posting Permissions

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