Average of grouping columns

Question

My table is something like this

id ...... amount...........food
+++++++++++++++++++++++++++++++++++++
1 ........ 5 ............. banana  
1 ........ 4 ............. strawberry  
2 ........ 2 ............. banana  
2 ........ 7 ............. orange  
2 ........ 8 ............. strawberry  
3 ........ 10 .............lime  
3 ........ 12 .............banana  

What I want is a table display each food, with the average number of times it appears in each ID.

The table should look something like this I think:

    food ........... avg............
    ++++++++++++++++++++++++++++++++
    banana .......... 6.3 ............
    strawberry ...... 6 ............
    orange .......... 7 ............
    lime ............ 10 ............

I'm not really sure on how to do this. If I use just avg(amount) then it will just add the whole amount column

Answer 1

Did you try GROUP BY?

SELECT food, AVG(amount) "avg"
  FROM table1
 GROUP BY food

Here is SQLFiddle

Output:

|       food |               avg |
|------------|-------------------|
|       lime |                10 |
|     orange |                 7 |
| strawberry |                 6 |
|     banana | 6.333333333333333 |