Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Kind of rank...perhaps

    Hello,

    my table looks like:

    Code:
    Product   YEAR  QUARTER  PRICE
    A         2006     1     100
    A         2006     2     110
    A         2006     3     120
    B         2006     1     200
    C         2006     3     600
    C         2006     4     610
    I need an AVG(Price) of all products for the year 2006, but under the following terms:

    When computing the average, don't count one specific product more than one time. Only use one products last price. In this case:
    (120 + 200 + 610) / 3 is the wanted average price. Any idea for a small and smart solution?

    Thank you very much!!

  2. #2
    Join Date
    Mar 2008
    Posts
    6
    Quote Originally Posted by silas
    Hello,

    my table looks like:

    Code:
    Product   YEAR  QUARTER  PRICE
    A         2006     1     100
    A         2006     2     110
    A         2006     3     120
    B         2006     1     200
    C         2006     3     600
    C         2006     4     610
    I need an AVG(Price) of all products for the year 2006, but under the following terms:

    When computing the average, don't count one specific product more than one time. Only use one products last price. In this case:
    (120 + 200 + 610) / 3 is the wanted average price. Any idea for a small and smart solution?

    Thank you very much!!
    I think this is inefficient, but here's is what I came up with -

    select avg(price) from mytable where quarter in (select max(quarter) from mytable group by product)

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    34
    I would agree with our friend CyberEveryday.com. If you are looking for the Maximum Price though (instead of last quarter), I would change the query to:

    Code:
    SELECT AVG(Price) FROM Products p WHERE p.price IN (SELECT MAX(Price) As MaxPrice FROM Products GROUP BY Product);
    Either way it is not very efficient.
    DigiOz Multimedia
    http://www.digioz.com

Posting Permissions

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