Get soled Products with counter in Magento

Question

I want to get all the products that are sold also with counter like if product1 with 3 (times soled ).I am trying code like this one :

$itemsCollection= Mage::getResourceModel('sales/order_item_collection')
    ->join('order', 'order_id=entity_id');

But How can i get the product counter with that ? My Grid Class method code :

protected function _prepareCollection()
  {
    $itemsCollection = Mage::getResourceModel($this->_getCollectionClass());
    $itemsCollection= Mage::getResourceModel('sales/order_item_collection')
    ->join('order', 'order_id=entity_id');

$itemsCollection->addExpressionFieldToSelect("total_sold" , 'SUM({{qty_ordered}} - {{qty_canceled}})',array("qty_ordered" => "qty_ordered",
                "qty_canceled" => "qty_canceled"));

$itemsCollection = $itemsCollection->getSelect()->group("product_id");
      $this->setCollection($itemsCollection);
      return parent::_prepareCollection();
  }

Now i got Unrecognized method 'setPageSize()' Error.

Answer 1

Untested by here we go

// Your code
$itemsCollection= Mage::getResourceModel('sales/order_item_collection')
    ->join('order', 'order_id=entity_id');

// sum qty ordered - the canceled qty as it's not really sold if it's canceled. 
// refunded also is debatable
$itemsCollection->addExpressionFieldToSelect("total_sold" , 'SUM({{qty_ordered}} - {{qty_canceled}})',array("qty_ordered" => "qty_ordered",
                "qty_canceled" => "qty_canceled"));

$itemsCollection->getSelect()->group("product_id"); // make sure we group

You will then be able to loop thought the collection and get the total qty per product

foreach($itemsCollection as $item){
            $totalSold = $item->getTotalSold();
        }