Please help SQL noob with query!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Please help SQL noob with query!

  1. #1
    Join Date
    Mar 2005
    Posts
    8

    Unhappy Please help SQL noob with query!

    Hi all i have a problem with a simple query im hoping one of you clever sql junkies can help me

    ok i have a product table and am listing product id with product cost like so:

    SELECT x.id,x.cost
    FROM product x

    Now the problem occurs when i want to add another column which lists tha average cost of items. I have tried this:

    SELECT x.id,x.cost,AVG(x.cost)
    FROM product x
    GROUP BY x.id,x.cost;

    I have also tried union. But to no avail :( i presume i am using totally the wrong technique? please point me in the right direction! thx muchly
    Last edited by sambucaboy; 03-23-2005 at 11:07 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997

    Re: Please help SQL noob with query!

    In this example the cost for each row would be equal to the average cost.
    You can not average one number against itself.

    Code:
    SELECT x.id,x.cost,AVG(x.cost)
    FROM cost x
    GROUP BY x.id,x.cost;
    If you had a detail table that showed what you sold the item for over a period of time, then you could average the cost and get a meaningful number. Using an outer join and an NVL to account for new items that have not been sold yet.

    Code:
    SELECT x.id, NVL(od.avg_cost, 0) avg_cost
      FROM cost x
      LEFT OUTER JOIN 
         ( SELECT product_id, AVG(cost) avg_cost
             FROM order_detail
            WHERE sale_date BETWEEN TO_DATE('01-JAN-2004') AND
                                    TO_DATE('01-JAN-2005')
            GROUP BY product_id ) od
        ON x.id = od.product_id
     GROUP BY x.id;
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2005
    Posts
    8
    thanks for that gandolf that was very useful im getting better at this by the day

    I think i didnt make it clear exactly what i wanted though. What i wanted was the third column to be the average cost of all items in the cost table. Therefore i could compare the cost of a particular item against the average cost of other items if you get me? Is there a way of doing this without creating another table?


    i.e the output i want:

    id cost av cost
    a1 10 20
    a2 20 20
    a3 30 20


    many thanks!...sb

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997

    Re: Re: Please help SQL noob with query!

    This should work.

    Code:
    SELECT x.id, x.cost, ( SELECT AVG(cost) cost
                             FROM cost ) avg_cost
      FROM cost x
    this space intentionally left blank

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    select
      p.id, p.cost, foo.avg_cost
    from
      product p,
      (select avg(cost) avg_cost from product) foo;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    Code:
    SELECT id, cost, AVG (cost) OVER () avg_cost 
    FROM   product;

  7. #7
    Join Date
    Mar 2005
    Posts
    8

    Talking

    Sweet! thanks a lot guys that was exactly what I needed to know. Very simple but could not find it in any of my books!

    Cheers....sb

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by jmodic
    Code:
    select
      p.id, p.cost, foo.avg_cost
    from
      product p,
      (select avg(cost) avg_cost from product) foo;
    Hey that's a cartesian product!!!
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width