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.