I got a table with the following content: Order_item

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:

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?

CodePudding user response：

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;

CodePudding user response：

You can use the result of your query to get the average:

WITH orders AS (
 select count(item-id) as items, order-id 
 from order_item
 group by order-id
)
SELECT AVG(items) FROM orders