CODEWITHSUNDEEP
mysqlsql
rasneb239
rasneb239

Reputation: 3

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

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?

Upvotes: 0

Views: 2135

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use group by as follows:

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

Upvotes: 1

juergen d
juergen d

Reputation: 204766

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

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

Upvotes: 2

Related Questions