Apply mysql query to Magento code

Question

How would I apply this query to Magento to display results on page. Basically I want to show the top 10 users total sales purchase amount

SELECT customer_id,SUM(base_grand_total) AS total_sales 
FROM sales_flat_order
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 10;

Answer 1

Try

$_collection = Mage::getModel('sales/order')->getCollection();
$_collection->getSelect()
 ->reset(Zend_Db_Select::COLUMNS)
 ->columns('customer_id')
 ->columns('SUM(base_grand_total) AS total_sales')
 ->order('total_sales')
 ->group('customer_id')
 ->limit(10);

or

/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');
/**
 * Retrieve the read connection
 */
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT customer_id,SUM(base_grand_total) AS total_sales 
 FROM sales_flat_order
 GROUP BY customer_id
 ORDER BY total_sales DESC
 LIMIT 10;';
/**
 * Execute the query and store the results in $results
 */
$results = $readConnection->fetchAll($query);

See https://stackoverflow.com/questions/5790811/aggregate-functions-in-magento-orm

Answer 2

Here is a quick little tutorial over a module called Clean SQL Reports. It lets you do arbitrary SQL reports in the magento admin section.