0

I have a mysql table structure with sample data in a way similar to below.

ID  |  ItemCode   | Cost      |
------------------------
1   |  A001       | 10        |
2   |  A001       | 20        |
3   |  A002       | 30        |
4   |  A002       | 40        |
5   |  A001       | 50        |
6   |  A002       | 60        |

The table holds data like this for a several hundreds item codes with cost history. I need to get the average cost of each itemcode of the last 2 (could be more as the table grows) entries for each item code.

How do I fill in the rest of the below line.

SELECT 
    ItemCode
    ,AVG(cost)
FROM 
    table_name 
WHERE 
    "the last 2 purchases for each itemcode" 
GROUP BY
    (ItemCode)

Expected result is

A001 is = (50 + 20) / 2 = 35

A002 is = (60 + 40) / 2 = 50.

Thank you very much for your help.

talegna
  • 2,407
  • 2
  • 19
  • 22
Ela Buwa
  • 1,652
  • 3
  • 22
  • 43
  • 2
    How do you identify the last? Do you have a date or auto_incremented field? – Filipe Silva Dec 05 '13 at 10:35
  • This has been asked **sooo many times here**!! Almost every day. The whole [greatest-n-per-group tag](http://stackoverflow.com/questions/tagged/greatest-n-per-group) is dedicated to this topic! – Tomas Dec 05 '13 at 10:37
  • @Tomas, i'd appreciate if you can send me a few links if so. – Ela Buwa Dec 05 '13 at 10:38
  • @FilipeSilva, each row has id's (primary key, auto increment) if it helps – Ela Buwa Dec 05 '13 at 10:39
  • The whole [greatest-n-per-group tag](http://stackoverflow.com/questions/tagged/greatest-n-per-group) is dedicated to this topic. – Tomas Dec 05 '13 at 10:41

0 Answers0