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.