I'm trying to create a graph that will show the average price of specific products on my site over a period of time. What I'm struggling with is coming up with the most efficient query to use, in order to get the information I need. I feel that Magento's querying method would be most efficient, so I'd like to know if that's possible first.

What's needed:

  • All products and their list price

    • Added OR updated in the last 30 days
    • Attribute set ID = 12
    • Type = virtual
  • Grouped by option ID from desired attribute ID

Example: Attribute ID / Code = 175 / Server

Options from that Attribute: Option ID = 22, 23, 24

I'd like them grouped into the option IDs.

Worth noting: Any item that has the attribute set ID of 12, must have the attribute 175 and one of the options selected.

I then would like to calculate the average of each option ID. So the average of 22 products, 23 products, 24 products, and so on.

I know it seems like a complex query, and I'm unsure of where to begin.


So I decided to go in a little different direction, but still accomplished what I was initially trying to do.

Worth noting: My site is a multi-vendor marketplace, so the products are those listed by different sellers, however the products the same, just slightly different prices.

My objective: Grab and save the average prices of specific products (all held in different arrays) at least once a day and save them to a new table in order to use them for comparisons/future graphs/charts.

One obstacle: I ran into a problem with special prices, as some sellers were using special prices, while others were using normal pricing. So if a seller had a special price set at the time of the cron, I wanted to use that instead of the normal price.

My query for calculating the averages:

    $array = array(11522, 9087, 10689, 7568, 12827, 3593, 2455, 12189); 
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $products = $resource->getTableName('vendor_products');
    $entityTable = $resource->getTableName('catalog_product_entity_int');

    $query1 = "SELECT AVG(price) 
          FROM (SELECT IF(c.`special_price` = '0', c.`price`, c.`special_price`) AS price 
          FROM `vendor_products` AS c 
          INNER JOIN `catalog_product_entity_int` AS e ON c.`product_id` = e.`entity_id` 
          WHERE e.value = 1 AND e.`attribute_id` = 96 AND e.entity_id IN (" . implode(',',$array) . ")) AS T";

    $avg1 = $readConnection->fetchOne($query1);


Explanation: I'm calculating the average, using the normal price column in the product table if the special_price column is set to 0 (no special price set). The array holds the specific product ID's I want averaged for this specific set of products. I have 7 queries exactly as the one above, all using a different array of product ID's. I have these queries set up in a script that runs once a day, and then saves each $avgX into a new table I set up to hold each average and the date it was added. So now, when I want to display these averages over a period of time, I can just pull them from the new table, along with the date those averages coincide with.

I took a different route than the original, because I decided not to use ALL items listed since the average could be easily manipulated by a seller who decided to list his item for 2-3 times what its actually worth. So I decided to pick specific items from several trusted sellers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.