SQL Average Number of Items per Order Query!!!

IntoTheVoid · 7 Sep 2014 at 22:00

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:

Code:

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

Any ideas anyone? Cheers.

GravyMonster · 7 Sep 2014 at 22:12

What's your table structure like? I'd guess you'd need to use SUM instead of COUNT assuming you have a Quantity field for each item row or the order. You'd probably also need a GROUP BY to group the items by OrderId.

Also, what form of SQL is this?

IntoTheVoid · 7 Sep 2014 at 22:16

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.

chroniclard · 7 Sep 2014 at 22:32

Something like

SELECT AVG(a.orderitem) as Average
FROM (SELECT count(*) as orderitem, OrderNumber
FROM Orders
group by OrderNumber)
AS a

IntoTheVoid · 7 Sep 2014 at 22:42

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

chroniclard · 7 Sep 2014 at 22:57

Works ok for me on SQL server and worked ok on an Access 2010 Query. Given the above dataset the average is 1, rounded down in SQL(can change it to show decimals if you like) and gives 1.2 on Access.

With your column names(don't know your table name)

SELECT AVG(a.orderitemcount) as Average
FROM (SELECT count(*) as orderitemcount, orderid
FROM tblOrders
group by orderid)
AS a

IntoTheVoid · 7 Sep 2014 at 22:59

thanks will try again. must be me

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

chroniclard · 8 Sep 2014 at 09:24

The SELECT statement in the brackets counts the numbers of items per order then the SELECT statement outside averages them.

IntoTheVoid · 8 Sep 2014 at 16:20

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:

Code:

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