How do I find the average of the result of a group by SQL query?

Question

So assuming I'm starting with a table called Inventory like the following where there are multiple unique items per non unique storage ID:

ItemID	StorageID
453	100
234	100
642	150
234	200
343	200
143	200

I group the items based on its storage ID so it would result in the following table - (select itemID, storageID from Inventory group by storageID)

ItemID	StorageID
453, 234	100
642	150
234, 343, 143	200

But then here is the part I'm stuck on: I want to return a single numerical result representing the average number of items per storage. So that would involve counting the number of items per each distinct storage (ie: storageID 100 has 2 items, storageID 200 has 3 items) and then finding an average. So in the example I shared, the average would be (2+1+3)/3 = 2.67 items/storage. How could I query MySQL for this? Do I even need to use group by as a start?

Answer 1

Group your data and use count() on each group.

select avg(cnt)
from
(
  select count(*) as cnt, storageID 
  from Inventory 
  group by storageID
) tmp

Answer 2

You can use group by as follows:

select avg(cnt) as average_
(select count(*) as cnt
  from your_table t
 group by storageid) t

Answer 3

You don't need a subquery:

select count(*) / count(distinct storageID) as avg_per_storageID
from Inventory ;

The average is the total number of items divided by the number of storageIDs.