SELECT AVG( items_num ) AS AvgItemsOrder FROM ( SELECT order_id, COUNT( item_id ) items_num FROM `sales_flat_order_item` GROUP BY order_id )ItemsOrder
Archive of posts tagged items per order
Magento SQL – Counting the number of items per order
SELECT sfo.increment_id, COUNT( item_id ) items_number FROM `sales_flat_order_item` AS sfoi INNER JOIN `sales_flat_order` AS sfo ON sfo.entity_id = sfoi.order_id GROUP BY order_id LIMIT 0 , 30