SQL Average Number of Items per Order Query!!!

[IntoTheVoid]

Hi, I have a table that lists Order Numbers and Order Items for each order number. I am trying to work out the average number of items per order, but am stuck in aggregate function errors. So for example there are 11 order items in total, and there are 4 different order numbers (with some listed multiple times), my calculation should calculate 11 / 4. I tried the following:

SELECT count(OrderItems) /count (DISTINCT OrderNumbers) AS AverageItems FROM tblOrders;

Any ideas anyone? Cheers.

 

[IntoTheVoid]

Hi, the table structure is:
OrderItem integer
OrderNumber integer

In this table there is no quantity field. This is Access SQL. In this example I am just trying to return a single number that represents the average number of order items per order. Cheers. I know this is a contrived example, but I have kept the table simple as I am learning SQL at the moment.

 

[IntoTheVoid]

I cannot get that to work. I have sample data such as follows:

OrderItem OrderID
1 2
5 5
5 1
8 3
6 3
12 4

so I am trying to calculate 6 order items / 5 different orders in this case.

Sorry for the formatting. the second number should fall under the order id column

 

[IntoTheVoid]

thanks will try again. must be me

Edit: it works now. thanks again. just need to get my head around it now.

 

[IntoTheVoid]

I can understand what is happening now, thanks for the insight. It is frustrating that SQL Server can get the same job done with simpler syntax i.e this works for me and the CAST function gives me the result without rounding down:

SELECT CAST(COUNT(OrderItems) AS FLOAT) / COUNT(DISTINCT NumOrders) 
AS Average_Items_Per_Order FROM tbl_Orders;