Get numbers of completed sales by product ID in Magento

0
votes

I have a .phtml file and i want to show all products of a category with the number of Completed sales and if possible the price x the number of sales so i can make a total for each product.

Now i have this code where i can see the product in a category and the product ID but i can't get it to work with the number of sales and the price.

<?php
$categoryid = 19;

$category = new Mage_Catalog_Model_Category();
$category->load($categoryid);
$collection = $category->getProductCollection();
$collection->addAttributeToSelect('*');

foreach ($collection as $_product) { ?>

   <a href="<?php echo $_product->getProductUrl() ?>"><img src="<?php echo $this->helper('catalog/image')->init($_product, 'small_image')->resize(200); ?>" width="200" height="200" alt="" /></a> <a href="<?php echo $_product->getProductUrl(); ?>"><?php echo $_product->getName(); ?><?php echo $_product->getId(); ?> </a>

<?php }?>

If someone could help me a little bit futher that would be great! Thanks!

1
magentoproduct

1 Answers

0
votes

Take a look at table sales_flat_invoice_item (or sales_flat_invoice_item)

You could create a custom query e.g

SELECT * from sales_flat_invoice_item 
WHERE product_id = YOUR_PRODUCT_ID
# GROUP BY order_id # if you have product with custom option