Kind of rank...perhaps

silas · #1 (**permalink**) 03-24-08, 13:45

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!!

CyberEveryday.com · #2 (**permalink**) 03-24-08, 14:21

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)

digioz · #3 (**permalink**) 03-25-08, 02:49

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech