Search code examples
sqlgroup-bycount

sql average on count result

I got a table with the following content: Order_item

Order-id item-id
1 45
4 45
4 57
5 68
5 32
6 68

I would like to know how many items are contained in average per order.

I tried that sql query:

select count(item-id), order-id 
from order_item
group by order-id

that got me the following result:

Order-id count
1 1
4 2
5 2
6 1

And now I would divide the 6 items of the count through 4 orders which gets me to my result of average 1,5 items per order. How would I write a SQL query to get the result 1,5?

Solution

  • Divide count of all rows by distinct IDs and multiply by 1.0 to implicitly cast for numeric division.

    select Count(*) / (Count(distinct Order_Id) * 1.0)
from Order_item;